calculate the percentage of sum of volume between current and the previous month

[alaghumeenal]

base search...
| eval Month = case(Month = "2018-02","Feb",Month = "2018-03","Mar", Month = "2018-04","Apr")
| eval month_curr=relative_time(now(), "-3mon")
| eval month_curr=strftime(month_curr, "%b")

| eval month_prev=relative_time(now(), "-4mon")
| eval month_prev=strftime(month_prev, "%b")
| chart sum(Volume) AS Vol over Shop by Month

Need to calculate percentage between Mar and Feb

[niketn]

@alaghumeenal following is a run anywhere search based on Splunk's _internal index, which uses streamstats to calculate percent increase/decrease based on previous month's count.

index=_internal sourcetype=splunkd log_level!="INFO"
| timechart span=1mon count
| streamstats last(count) as prev_count window=1 current=f
| eval perc=round(((prev_count-count)/prev_count)*100,2)
| where isnotnull(perc)

Please change as per your use case.

[woodcock]

I would actually use | delta count AS prev_count instead.

[alaghumeenal]

Hi can you please help, the value is not getting subsititued for the month_prev, month_curr, so the percentage is not getting calculated properly.
Note: _time does not have the proper timestamp. So i have to calculate based on the Activation_Month

[dperre_splunk]

Can you share some sample data?

[niketn]

@alaghumeenal, since you did not mention Activation_Month in your original query I am assuming it is same as Month field in your query and has Time information like 2018-02, 2018-03 etc.

The above logic should work with Activation_Month as well as far as it is sorted in Ascending order. Try the following search and confirm!

<yourBaseSearch> Activation_Month=*
| sort Activation_Month
| stats count by Activation_Month
| streamstats last(count) as prev_count window=1 current=f 
| eval perc=round(((prev_count-count)/prev_count)*100,2) 
| where isnotnull(perc)

[alaghumeenal]

what will be the values for count and prev count, how is it calculated?
Apologies, it is Month column. I tried the below search command and it says no results found.

| eval Month = case(Month = "2018-02","Feb",Month = "2018-03","Mar", Month = "2018-04","Apr")
| sort Month
| stats count by Month
| streamstats last(count) as prev_count window=1 current=f
| eval perc=round(((prev_count-count)/prev_count)*100,2)
| where isnotnull(perc)

[alaghumeenal]

Below is the sample data that i have used in the csv file.

Shop Month Volume
AAA 2018-04 23
AAA 2018-04 10
AAA 2018-03 75
AAA 2018-04 89
AAA 2018-02 20
AAA 2018-02 10
BBB 2018-03 5
BBB 2018-04 7
BBB 2018-02 6
BBB 2018-03 8
BBB 2018-04 23
BBB 2018-02 1
BBB 2018-03 0
BBB 2018-04 7
CCC 2018-02 8
CCC 2018-02 14
CCC 2018-04 32
CCC 2018-02 4
CCC 2018-03 87

Also i have used the below search Query where i have manually calculated the percentage of volume sold in shops over the last two months. But when my data changes every month i cannot change the search. So please guide me to dynamically calculate the percentage of the previous two months from the current month.

| eval Month = case(Month = "2018-02","Feb",Month = "2018-03","Mar", Month = "2018-04","Apr")
| chart sum(Volume) AS Vol over Shop by Month
| eval Percent = round(((Apr-Mar)/Mar)*100, 0)
| fields Shop, Percent, Feb Mar Apr

[renjith_nair]

Hi @alaghumeenal ,

Try,

base search...
| eval Month = case(Month = "2018-02","Feb",Month = "2018-03","Mar", Month = "2018-04","Apr")
| eval month_curr=relative_time(now(), "-3mon")
| eval month_curr=strftime(month_curr, "%b")
| eval month_prev=relative_time(now(), "-4mon")
| eval month_prev=strftime(month_prev, "%b")
| chart sum(Volume) AS Vol over Shop by Month
| streamstats current=f last(Vol) as prev |eval diff=((Vol-prev)/Vol)*100

[woodcock]

I would actually use | delta Vol AS prev instead.

[alaghumeenal]

When i execute the above search I am not getting any results. Below is the search that i had used previously
| eval Month = case(Month = "2018-02","Feb",Month = "2018-03","Mar", Month = "2018-04","Apr")
| eval month_curr=relative_time(now(), "-4mon")
| eval month_curr=strftime(month_curr, "%b")

| eval month_prev=relative_time(now(), "-5mon")
| eval month_prev=strftime(month_prev, "%b")
| chart sum(Volume) as Volume over Shop by Month
| eval Percent = round(((Mar-Feb)/Feb)*100, 0)
| fields Shop, Percent, Feb Mar Apr

But i have hard coded the months directly. Instead i need to calculate the percentage between past two months based on teh current month.

[renjith_nair]

Regardless of what you have used to get the result, adding | streamstats current=f last(Vol) as prev |eval diff=((Vol-prev)/Vol)*100
should work if you have the result in below format

Feb Vol
Mar Vol
Apr Vol