How to compare field values from this year vs last year by date and calculate the percentage change?

[puneetkharband1]

Hi,

I have data in 2 fields in table: one is date and the other is some value, for each year respectively.
Now I want to perform an action like compare date_1 from 2015 vs date_1 from 2016, then perform some evals on the data.

For example:
01-01-2015 1234567
02-01-2015 1234578

01-01-2016 1234563
02-01-2016 1234577

Now I want to compare 01-01-2015 with 01-01-2016, see if they are equal greater less, and do a percentage change based on the data.

Sample:

index="test" source="test" year= date= mon=* SITE=US | stats values(field) as auth by year, mon, date | stats list(mon) list(date) values(auth) by year

this is what i tried
this is how raw data looks like
2016-12-31 00:00:00.000, date="2016-12-31 00:00:00.0", field="1234567", SITE="US"

I have extracted year month and date as part of time values

I do not have any time values but date year month thats what matters as I need to compare the numbers from last year

Please recommend

I have pulled all the dates using stats values by date and appended by a similar fashion for each year by adding the where clause in the date range.

[somesoni2 · Accepted solution]

Assuming you get one record for each day and your _time field represents correct date (the one you want to use), try like this

index="test" source="test" year=* date=* mon=* SITE=US
| eval day=strftime(_time,"%m-%d") 
| chart values(field) over day by year
| eval difference='2016'-'2015' 
| eval "%difference"=difference*100/'2015'

[dpatiladobe]

In these case year was constant if we are getting per day and date will keep change how we will calculate difference ? as in "difference='2016'-'2015'" . 2016 and 2015 are constant , considering dynamically generated how we can handle same ?Also adding to Puneet concern how we can compare more than 2 years ?

[dpatiladobe]

We can do eval to put things into bucket and name them so that name can be used in calculation.
like below for week to week comparison

search |eval marker=if (_time<relative_time(now(),"-w@w"), "Last2Week_Weekdays","LastWeek_Weekdays") | eval _time=if(marker=="Last2Week_Weekdays", _time + 7*24*60*60, _time)

[puneetkharband1]

Actually what you did is great and I miss something as part of my requirement is this sample is only for one year
I have data for 3-4 years so in that case how difference will work I am not sure. as I need to compare data for all 3 years in one shot.

[Sukisen1981]

your _time is only monthly?

[puneetkharband1]

index="test" source="test" year=* date=* mon=* SITE=US | stats values(field) as auth by year, mon, date | stats list(mon) list(date) values(auth) by year

this is what i tried
this is how raw data looks like
2016-12-31 00:00:00.000, date="2016-12-31 00:00:00.0", field="1234567", SITE="US"

I have extracted year month and date as part of time values

I do not have any time values but date year month thats what matters as I need to compare the numbers from last year