Weekly change percentage query

[dhavamanis]

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,  -

[somesoni2 · Accepted solution]

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