Computing column differences

[ldnail_at_TI]

This is result of a query that reflects license consumption by day

Index	3/2/2021	3/3/2021	3/4/2021	3/5/2021	3/6/2021	3/7/2021	3/8/2021
index01	0.018006	0.018128	0.018065	0.018035	0.017944	0.017985	0.018042
index02	0.014985	0.009444	0.054803	0.010401	0.006807	0.005035	0.008998
index03	3.468919	3.674277	3.786565	3.133193	2.151094	2.548173	4.531934
index04	0.084911	0.082637	0.090785	0.062404	0.012795	0.031198	0.084129

I'm try to compute a daily difference so we can easily spot variance/trend with the result looking something like this:

Index	3/2/2021	3/3/2021	dif day 1	3/4/2021	dif day 2	3/5/2021	dif day 3	3/6/2021	dif day 4	3/7/2021	dif day 5	3/8/2021	dif day 6
index01	0.018006	0.018128	-0.00012	0.018065	0.00006	0.018035	0.00003	0.017944	0.00009	0.017985	-0.00004	0.018042	-0.00006
index02	0.014985	0.009444	0.005541	0.054803	-0.04536	0.010401	0.04440	0.006807	0.00359	0.005035	0.00177	0.008998	-0.00396
index03	3.468919	3.674277	-0.20536	3.786565	-0.11229	3.133193	0.65337	2.151094	0.98210	2.548173	-0.39708	4.531934	-1.98376
index04	0.084911	0.082637	0.002274	0.090785	-0.00815	0.062404	0.02838	0.012795	0.04961	0.031198	-0.01840	0.084129	-0.05293

The query I started from is below & I've tried 20 ways to Sunday get a difference column, but no joy

index=_internal source=*license_usage.log* type=Usage earliest=-7d@d latest=@d host=licenseserver
| eval GB=round(b/1024/1024/1024,6)
| bucket span=1d _time
| eval Time=strftime(_time,"%m/%d/%y")
| chart sum(GB) AS volume_GB over Time by idx limit=0
| transpose 0 column_name=Index header_field=Time

I'm not married to chart or transpose, its just where it all started.

Any suggestions?

[ldnail_at_TI]

that did the trick... thanks a bunch.

[ITWhisperer · Accepted solution]

Try something like this

index=_internal source=*license_usage.log* type=Usage earliest=-7d@d latest=@d host=licenseserver
| eval GB=round(b/1024/1024/1024,6)
| bucket span=1d _time
| stats sum(GB) as volume_GB by idx _time
| streamstats window=2 list(volume_GB) as diff by idx
| eval diff=tonumber(mvindex(diff,0)) - tonumber(mvindex(diff,1))
| eval volume_GB=mvappend(volume_GB,diff)
| xyseries idx _time volume_GB
| foreach * [ eval <<FIELD>>_diff=if(mvcount('<<FIELD>>') > 1, mvindex('<<FIELD>>', 1), null) | eval <<FIELD>>=mvindex('<<FIELD>>',0) ]
| transpose 0 header_field=idx
| eval column=if(match(column,"_"),strftime(mvindex(split(column,"_"),0),"%m/%d/%Y diff"),strftime(column,"%m/%d/%Y"))
| transpose 0 header_field=column