To compare the value of particular day with the same day of the last week and last to last week.

[Real_captain]

Hi 
Can someone please tell me how we can compare the value of a particular day with the value of the same day of last week and create a new field as deviation. 

Example : 
Below command generates the output as below : 

| stats sum(Number_Events) as TOTAL by FIeld1 FIeld2  FIeld3 Day  Time Week_of_year Total

We need the output like below : 

1. In tabular form : Is it possible to have an output like below : 

2. If point 1 is possible to be created , then Is it possible to have a time-chart with 3 lines over the 24 hours of the day . Example of data for 3 hours is attached 
1 line corresponds to Week of year -2 (39)
2nd line corresponds to Week of year -1 (40)
3rd line corresponds to Week of year (41)

Thanks in advance to help me out. 

[ITWhisperer]

| stats count as Total by field1 field2 field3 Day Time Week
| eval Week_{Week} = Total
| stats values(Week_*) as Week_* by field1 field2 field3 Day Time
| fillnull value=0
| eval Deviation=2*Week_41/(Week_39+Week_40)

[Real_captain]

HI @ITWhisperer 
Thanks for the response. 

But instead of hard-coading the week number to generate the deviation 

 | eval Deviation=2*Week_41/(Week_39+Week_40)  the week 

Can we dynamically give the dynamic value of the week as below : 

 | eval Deviation=2*Week_{current_week}/(Week_{current_week - 1} +Week_{current_week - 2})

Thanks in advance. 

[ITWhisperer]

Just re-evaluate Week after the stats command to be current week, current week -1 and current week -2 as appropriate

[Real_captain]

Hi @ITWhisperer 
Can you please let me how can I correct the below stats command to re-evaluate Week after the stats command to be current week, current week -1 and current week -2. 

| stats count as Total by field1 field2 field3 Day Time Week
| eval Week_{Week} = Total
| stats values(Week_*) as Week_* by field1 field2 field3 Day Time
| fillnull value=0
| eval Deviation=2*Week_41/(Week_39+Week_40)

[ITWhisperer]

| stats count as Total by field1 field2 field3 Day Time Week
| eventstats max(Week) as ThisWeek
| eval Week=if(Week=ThisWeek,"CurrentWeek","CurrentWeek".(Week-ThisWeek))
| eval {Week} = Total
| stats values(Current*) as Current* by field1 field2 field3 Day Time
| fillnull value=0
| eval Deviation=2*CurrentWeek/('CurrentWeek-2'+'CurrentWeek-1')

[Real_captain]

@ITWhisperer  : Thanks It worked. You are best 🙂

Just a small correction related to the order of columns.
Is it possible to have currentweek-4 column first, then currentweek-3 , then currentweek-2  , then currentweek-1 and , currentweek in the end before Deviation. 

 

[ITWhisperer]

Just append a table command listing the fields in the order you want them

[Real_captain]

Hi @ITWhisperer 
As CurrentWeek* is calculated dynamically , how can i put that field in the table command. 
If i use the below table command , it shows me the result in the same order. 
How can i reverse the order of the CurrentWeek columns ??? 

| table A B C DAY_OF_WEEK Start_Time CurrentWeek* Deviation

[ITWhisperer]

Don't use wildcards or at least not for the -1 -2 -3 part - currently in your screenshot all the names are fixed