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!

What’s new on Splunk Lantern in August

This month’s Splunk Lantern update gives you the low-down on all of the articles we’ve published over the past ...

Welcome to the Future of Data Search & Exploration

You have more data coming at you than ever before. Over the next five years, the total amount of digital data ...

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

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