Use eval (or other) to make automatic calculation in xyseries

[goncalosilva123]

Hi,

I have an automatic process that daily writes  some information in a CSV file [1]. Then I have a dashboard that picks up some data and uses xyseries so that I can see the evolution by day. [2]

Now I want to calculate the difference between everyday, but the problem is that I don't have "field" names so that I can use in an Eval, so I can I solve this? [3]

Please see examples below.

################################################

[1] Information Saved every day [ | is the separator of each column]

[Item_Name] | [Date_1] | [Total]
2021-01-18
Item 1 | |2021-01-18 | 32
Item 2 | |2021-01-18 | 50
Item 3 | |2021-01-18 | 10
Item 4 | |2021-01-18 | 15

2021-01-19
Item 1 | |2021-01-18 | 29
Item 2 | |2021-01-18 | 37
Item 3 | |2021-01-18 | 8
Item 4 | |2021-01-18 | 10

2021-01-20
Item 1 | |2021-01-20 | 31
Item 2 | |2021-01-20 | 25
Item 3 | |2021-01-20 | 5
Item 4 | |2021-01-20 | 13

################################################
| inputlookup blabla
| xyseries Item_Name Date_1 Total

[2] Applying xyseries I get the following

              2021-01-18      2021-01-19       2021-01-20 

Item 1 32                          29                          31
Item 2 50                          37                          25
Item 3  10                          8                            5
Item 4 15                          10                          13

################################################

[3] What I would like to have

              2021-01-18      2021-01-19       2021-01-20       Dif_18_19       Dif_19_20 

Item 1 32                          29                          31                           -3 (29-32)        2 (31-29)
Item 2 50                          37                          25                           -13 (37-50)     -12 (25-37)
Item 3  10                          8                            5                              -2 (8-10)        -3 (5-8)
Item 4 15                          10                          13                           -5 (10-15)        3 (13-10)

################################################

Thanks in advance.

Best Regards,

[tscroggins · Accepted solution]

@goncalosilva123 

Here's an alternative with similar performance:

| inputlookup a_CSV_file.csv
| streamstats current=f last(Date_1) as last_Date_1 last(Total) as last_Total by Item_Name
| eval Diff_Date="Diff_".substr(last_Date_1, -2)."_".substr(Date_1, -2)
| appendpipe [ | stats values(eval((Total - last_Total)." (".Total."-".last_Total.")")) as Diff by Diff_Date Item_Name ]
| eval Date_1=coalesce(Date_1, Diff_Date), Total=coalesce(Total, Diff)
| xyseries Item_Name Date_1 Total

[tscroggins]

@goncalosilva123 

Insert a tail command between streamstats and eval:

| tail 2

If your input CSV is very large, you can additionally limit the results before streamstats with an inputlookup where predicate, a separate where command, or even | tail 3.

| inputlookup

| tail 3

| streamstats

| tail 2

| eval

[goncalosilva123]

Thanks for all!

Finally, how can I change order based on the most recent date and number (2021-01-20, descending) and also change the cell color based on the value?
Ex:
-> If the cell value is bigger than 5, puts cell color red, If cell color is between 0 and 5, puts orange, if is less than 0, puts green.
-> 2021-01-20
     31
     25
     13
     5     

#################################################################
The goal!

	2021-01-19	2021-01-20	Dif_19_20
Item 1	29	31	-2 (31-29) (Green)
Item 2	37	25	-12 (25-37) (Green)
Item 4	10	13	3 (13-10) (Red)
Item 3	8	5	-3 (5-8) (Green)

[goncalosilva123]

Hi again,

this as done the work...and just another question, if I have several dates, how can I show only the last two and the difference?

ex:

               2021-01-19       2021-01-20       Dif_19_20 

Item 1 29                          31                           -2 (31-29)
Item 2 37                          25                           -12 (25-37)
Item 3 8                            5                              -3 (5-8)
Item 4 10                          13                           3 (13-10)

Thanks in advance and sorry for the delay in answer.

[tscroggins]

@goncalosilva123 

Use a method appropriate to your source data to filter the base search prior to streamstats:

| inputlookup a_CSV_file.csv
| eval _time=strptime(Date_1, "%F")
| eventstats max(_time) as max_time
| where _time>relative_time(max_time, "-2d")
| streamstats current=f count last(Date_1) as last_Date_1 last(Total) as last_Total by Item_Name
| eval Diff_Date="Diff_".substr(last_Date_1, -2)."_".substr(Date_1, -2)
| appendpipe [ | stats values(eval((Total - last_Total)." (".Total."-".last_Total.")")) as Diff by Diff_Date Item_Name ]
| eval Date_1=coalesce(Date_1, Diff_Date), Total=coalesce(Total, Diff)
| xyseries Item_Name Date_1 Total

[to4kawa]

| makeresults
| eval _raw="item,date,count
Item 1  ,2021-01-18  ,32
Item 2  ,2021-01-18  ,50
Item 3  ,2021-01-18  ,10
Item 4  ,2021-01-18  ,15
Item 1  ,2021-01-19  ,29
Item 2  ,2021-01-19  ,37
Item 3  ,2021-01-19  ,8
Item 4  ,2021-01-19  ,10
Item 1  ,2021-01-20  ,31
Item 2  ,2021-01-20  ,25
Item 3  ,2021-01-20  ,5
Item 4  ,2021-01-20  ,13"
| multikv forceheader=1
| eval date=trim(date)
| table item date count
``` this is sample data. from here, the logic ```
| streamstats current=f last(count) as delta_value by item
| eval diff_field="Diff_".mvindex(split(date,"-"),-1)."-".strftime(relative_time(strptime(date,"%F"),"-1d"),"%d")
| eval diff_value=count - delta_value
| eval tmp1=date.":".count, tmp2=diff_field.":".diff_value
| eval tmp=mvappend(tmp1,tmp2)
| table item tmp
| mvexpand tmp
| rex field=tmp "(?<date>.*):(?<count>.+)"
| xyseries item date count