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,
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
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
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
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) |
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.
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
| 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