Solved: ROLLING 16 Months Growth - Qlik Community

Report Inappropriate Content · ‎2014-02-27

Hi

I want to represent Last 16 Month Rolling Growth%.

I have Sales of Products for 3 Years.

Growth will be calculated as

for Eg. Oct 2013 Growth= (Oct 2013 Sales - Oct 2012 Sales)/Oct 2012 Sales.

I have to use Month Year as Dimension.

Thanks and Regards

hic · ‎2014-02-28

Assuming that you have YearMonth as dimension and that YearMonth is a date serial number (which it is in your Excel sheet), then you can calculate this month's number as

Sum(Sales)

and the number for the month 12 months ago as

Sum(Aggr(Rangesum(Above(Sum(Sales),12,1)),YearMonth))

So your expression becomes

Sum(Sales)/Sum(Aggr(Rangesum(Above(Sum(Sales),12,1)),YearMonth)) - 1

You need to make sure that your YearMonths are loaded in chronological order though, since the Aggr() function internally sorts according to load order.

You cannot use the $-expansion with a formula, since it is not sensitive for the dimensional scope.

HIC

View solution in original post

sunilkumarqv · ‎2014-02-27

You can follow below Link . Henric Post

http://community.qlik.com/blogs/qlikviewdesignblog/2013/06/10/relative-calendar-fields

Sum( {$<Date={">=$(=MonthStart(AddMonths(Max(Date),-16)))<$(=MonthEnd(Max(Date)))"}>} Sales)

PrashantSangle · ‎2014-02-27

hi,

Rolling 12 months=(sum({<CalendarDate={"$(=Date(Max(CalendarDate)))"}>} Sales)

-sum({<CalendarDate={"$(=Date(AddMonths(max(CalendarDate),-12)))"}>}Sales))

/sum({<CalendarDate={"$(=Date(AddMonths(max(CalendarDate),-12)))"}>}Sales)

try this set analysis.

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Report Inappropriate Content · ‎2014-02-27

but if i will use Month Year(eg. oct 2013) as Dimension i will not get the desired Result.

Report Inappropriate Content · ‎2014-02-27

but if i will use Month Year(eg. oct 2013) as Dimension i will not get the desired Result and i want to calculate sales growth which is a calculated expression.

sunilkumarqv · ‎2014-02-27

Hi Himank,

Please follow below link Calculating rolling n-period totals, averages or other aggregations its Realy Good

You may get solution

Report Inappropriate Content · ‎2014-02-27

Use makedate function in the set analysis. that is make date from Oct 2013

Report Inappropriate Content · ‎2014-02-27

Dear Sunil

I have gone through it and i understand that i can calculate rolling months Sales but i want to know how can i calculate Rolling Months Growth% with Dimension as Month Year

PrashantSangle · ‎2014-02-27

Hi,

if possible can you upload sample data with desired result

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Report Inappropriate Content · ‎2014-02-27

Hi

I have attached an Excel where i have specified Input and what output i want in a straight table or bar chart.

Thanks and Regards

ROLLING 16 Months Growth

QlikView App Dev