chart sums by month, then difference current month from preceding month

[swangertyler]

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|

[renjith_nair · Accepted solution]

@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]

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.

[renjith_nair]

@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

[swangertyler]

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