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
@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.
I would actually use | delta count AS prev_count
instead.
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
Can you share some sample data?
@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)
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)
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
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
I would actually use | delta Vol AS prev
instead.
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.
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