I need to make a table where I have four columns, the group, the current month, the previous month, and the difference between the two. I have the first three columns, however,I cannot figure out how to calculate the difference.
| eval impactduration = floor((impact_stop-impact_start)/60) | bucket _time span=1mon
| stats sum(impactduration) as "Impact duration in Minutes" by product_group, _time
|eval month=strftime(_time, "%b")
| maketable product_group month "Impact duration in Minutes"
The desired result is something like
|product_group|Oct |Sept|difference|
|X | 100 | 90 | -10|
|Y|60 |100|40|
@swangertyler ,
Try
| eval impactduration = floor((impact_stop-impact_start)/60) | bucket _time span=1mon
| stats sum(impactduration) as impactduration by product_group, _time
| eval month=strftime(_time, "%b")
| streamstats current=f window=1 last(impactduration) as prev by product_group|eval Difference=prev-impactduration
| maketable product_group,month,impactduration,Difference
You may rename the fields as necessary
@swangertyler ,
Try
| eval impactduration = floor((impact_stop-impact_start)/60) | bucket _time span=1mon
| stats sum(impactduration) as impactduration by product_group, _time
| eval month=strftime(_time, "%b")
| streamstats current=f window=1 last(impactduration) as prev by product_group|eval Difference=prev-impactduration
| maketable product_group,month,impactduration,Difference
You may rename the fields as necessary
This works. Thank you @renjith.nar
Now I am trying to figure out how to account for "blanks". I have product_group values that exist in the current month, but not the previous month. Because of this, I have empty values that I would like to fill with zeros. However, I cannot identify where a fillnull
fixes my problem.
@swangertyler , try using timechart
instead of stats which will resolve most of the complications
Just give this a try and compare the results
| eval impactduration = floor((impact_stop-impact_start)/60)
| timechart span=1mon sum(impactduration) as impactduration by product_group
|untable _time,product_group,impactduration |sort product_group
| eval month=strftime(_time, "%b")
| streamstats current=f window=1 last(impactduration) as prev by product_group
|eval Difference=prev-impactduration
| maketable product_group,month,impactduration,Difference
with a little massaging of timechart and add a fillnull, I got it to do what I needed. Thank you for the help. I very much appreciate it.
| eval impactduration = floor((impact_stop-impact_start)/60)
| timechart span=1mon sum(impactduration) as impactduration by product_group useother=f limit=20|fillnull value=0
|untable _time,product_group,impactduration |sort product_group
| eval month=strftime(_time, "%b")
| streamstats current=f window=1 last(impactduration) as prev by product_group
|eval Difference=prev-impactduration
| maketable product_group,month,impactduration,Difference