Splunk Search

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

alaghumeenal
New Member

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

Tags (1)
0 Karma

niketn
Legend

@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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

woodcock
Esteemed Legend

I would actually use | delta count AS prev_count instead.

0 Karma

alaghumeenal
New Member

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

0 Karma

dperre_splunk
Splunk Employee
Splunk Employee

Can you share some sample data?

0 Karma

niketn
Legend

@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)
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

alaghumeenal
New Member

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)

0 Karma

alaghumeenal
New Member

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

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

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
Happy Splunking!
0 Karma

woodcock
Esteemed Legend

I would actually use | delta Vol AS prev instead.

0 Karma

alaghumeenal
New Member

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.

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

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

Happy Splunking!
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...