Splunk Search

Weekly change percentage query

dhavamanis
Builder

Can you please help us to find the weekly change percentage, we have a splunk query

 index="mobileapps" sourcetype="mobileiostsv" Title="*" Brand="*" Status="Published" NOT (Type="Update" OR Type="In-App             Purchase") | eval week = strftime(relative_time(_time,"@w0"),"%m/%d/%Y")
 | stats sum(Units) as Downloads values(Brand) as Brand by Title week
 | append [search index="mobileapps" sourcetype=mobileiostsv Title="*" Brand="*" Status="Published" Type="Update" NOT Type="In-App Purchase" | eval week = strftime(relative_time(_time,"@w0"),"%m/%d/%Y")
 | stats sum(Units) as Updates values(Brand) as Brand by Title ]| stats values(Brand) as Brand values(Downloads) as Downloads values(Updates) as Updates by Title week
 | rename Title as App | addtotals | sort by App week  | fillnull value="-"

and its giving output with these fields,

 App
 Brand
 Week
 Downloads
 Updates
 Total

from this we want to find the weekly change percentage, like below output,

 App1, Brand1, 04/17/2016, 50, 50, 100, 10%
 App1, Brand1, 04/10/2016, 40, 50, 90,  12.5%
 App1, Brand1, 04/03/2016, 30, 50, 80,  -
 App2, Brand2, 04/17/2016, 40, 50, 90,  77.78%
 App2, Brand2, 04/10/2016, 10, 10, 20,  -
 App3, Brand3, 04/17/2016, 10, 10, 20,  -75%
 App3, Brand3, 04/10/2016, 10, 10, 80,  -

Also with another one report order by week date only with all the columns.

 App1, Brand1, 04/17/2016, 50, 50, 100, 10%
 App2, Brand2, 04/17/2016, 40, 50, 90,  77.78%
 App3, Brand3, 04/17/2016, 10, 10, 20,  -75%
 App1, Brand1, 04/10/2016, 40, 50, 90,  12.5%
 App2, Brand2, 04/10/2016, 10, 10, 20,  -
 App3, Brand3, 04/10/2016, 10, 10, 80,  -
 App1, Brand1, 04/03/2016, 30, 50, 80,  -
0 Karma
1 Solution

somesoni2
Revered Legend

Try something like this

For requirement 1

Your current search | sort 0 App Brand week | streamstats current=f window=1 values(Total) as prev by App Brand | eval change=coalesce(round((Total-prev)*100/prev,1),"-") | fields - prev  | sort 0 App Brand  -week

For requirement 2

Your current search | sort 0 App Brand week | streamstats current=f window=1 values(Total) as prev by App Brand | eval change=coalesce(round((Total-prev)*100/prev,1),"-") | fields - prev  | sort 0  -week App Brand

View solution in original post

somesoni2
Revered Legend

Try something like this

For requirement 1

Your current search | sort 0 App Brand week | streamstats current=f window=1 values(Total) as prev by App Brand | eval change=coalesce(round((Total-prev)*100/prev,1),"-") | fields - prev  | sort 0 App Brand  -week

For requirement 2

Your current search | sort 0 App Brand week | streamstats current=f window=1 values(Total) as prev by App Brand | eval change=coalesce(round((Total-prev)*100/prev,1),"-") | fields - prev  | sort 0  -week App Brand
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...