Difference of two columns with dynamic names

[rajashekar_s]

I am looking for a way to find the difference between two columns which are dynamic in values(time based).
Here is how the query looks like-
index=_internal
| eval weeknum=strftime(time_field,"%Y-%V")
| chart count over sourcetype by weeknum
| addtotals 2*
| sort- Total
| head 4

Sample table
sourcetype 2018-03 2018-04 2018-05 2018-06 Total
a 2 3 4 5 14
b 2 1 3 5 11
c 0 0 4 5 9
d 1 1 2 3 7

I am looking to calculate the difference between column 2 (2018-03) and column 5 (2018-06).
As the time picker can be given with any input, the weeknum is dynamic and I need the way to calculate difference with the dynamic names of the column.

[somesoni2 · Accepted solution]

Try this alternate implementation (Try to add one line at a time to your search to understand query functionality)

index=_internal
| eval weeknum=strftime(time_field,"%Y-%V")
| stats count by sourcetype weeknum
| streamstats count as sno by sourcetype
| appendpipe [| where sno=1 OR sno=4| chart values(count) over sourcetype by sno | eval count='1'-'4' | eval weeknum="Change" | table sourcetype weeknum count ]
| xyseries sourcetype weeknum count
| addtotals 2*

[rajashekar_s]

This technically works for me and is what I was looking for. But the time period is actually not a standard 4 weeks duration. The end user can change it to any duration.
Is it possible to incorporate dynamic weeknum too?

[somesoni2]

Assuming you always want difference of first weeknum and last (recent) weeknum, so try this variation:

index=_internal
 | eval weeknum=strftime(time_field,"%Y-%V")
 | stats count by sourcetype weeknum
 | streamstats count as sno by sourcetype
 | eventstats max(sno) as max by sourcetype
 | appendpipe [| where sno=1 OR sno=max | eval sno=if(sno=1,"first","recent") | chart values(count) over sourcetype by sno | eval count='first'-'recent' | eval weeknum="Change" | table sourcetype weeknum count ]
 | xyseries sourcetype weeknum count
 | addtotals 2*

[rajashekar_s]

That worked. Thanks a lot