How to find a difference of a column field by date

[bhava2704]

Name	perc	date
xxx	90	28-Dec-23
yyy	91	28-Dec-23
zzz	92	28-Dec-23
xxx	96	29-Dec-23
yyy	97	29-Dec-23
zzz	98	29-Dec-23

i want to calculate the difference between perc column value based on date,

for example, xxx have 90 in perc column for 28 dec 2023 and 96 for 29 dec 2023.  96-90= 6 will be the output .can you please help me with solution for my query.

additional query is i want to subtract the current date perc with yesterday date perc value. please assist me on this

[tscroggins]

Hi @bhava2704,

Given your sample data:

| makeresults format=csv data="Name,perc,date
xxx,90,28-Dec-23
yyy,91,28-Dec-23
zzz,92,28-Dec-23
xxx,96,29-Dec-23
yyy,97,29-Dec-23
zzz,98,29-Dec-23"
| eval _time=strptime(date, "%d-%b-%y")

you can use streamstats, timechart and autoregress, timechart and timewrap, etc. The timewrap command depends on the search earliest and latest times, so I've set them to 2023-12-28 and 2023-12-29, respectively.

When using streamstats, be mindful of the event order. In the example, your results are sorted by Date/_time ascending. In a normal event search, your results will be sorted by _time descending, and you'll need to adjust streamstats etc. arguments accordingly.

| streamstats global=f window=2 first(perc) as perc_p1 by Name
| eval delta_perc=perc-perc_p1

or

| timechart fixedrange=f span=1d values(perc) by Name
| autoregress xxx p=1
| autoregress yyy p=1
| autoregress zzz p=1
| eval delta_xxx=xxx-xxx_p1, delta_yyy=yyy-yyy_p1, delta_zzz=zzz-zzz_p1

or

| timechart fixedrange=f span=1d values(perc) by Name
| timewrap 1d
| eval delta_xxx=xxx_latest_day-xxx_1day_before, delta_yyy=yyy_latest_day-yyy_1day_before, delta_zzz=zzz_latest_day-zzz_1day_before

[ITWhisperer]

Do you mean something like this?

| stats range(perc) as range by Name

[tscroggins]

--with the caveat that range() values are always positive, i.e. abs(x-y).

[marnall]

The delta command seems like it goes in the right direction, but the only problem is that it can't be told to do separate deltas based on the values of other fields. If you don't have too many different Name fields, you could separate the perc fields into differently named fields and then do deltas on each one. This also requires you to sort by Name and then sort back to whichever your preferred sorting order is after the delta operation.

| sort Name
| eval perc_xxx = if(Name="xxx",perc,perc_xxx)
| eval perc_yyy = if(Name="yyy",perc,perc_yyy)
| eval perc_zzz = if(Name="zzz",perc,perc_zzz)
| delta perc_xxx as delta_perc
| delta perc_yyy as delta_perc
| delta perc_zzz as delta_perc
| fields - perc_*
| sort date