topic Re: ROLLING 16 Months Growth in QlikView

ROLLING 16 Months Growth

Thu, 27 Feb 2014 06:46:19 GMT

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

Re: ROLLING 16 Months Growth

sunilkumarqv — Thu, 27 Feb 2014 07:05:19 GMT

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)

Re: ROLLING 16 Months Growth

PrashantSangle — Thu, 27 Feb 2014 07:09:31 GMT

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.

Re: ROLLING 16 Months Growth

Thu, 27 Feb 2014 07:12:06 GMT

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

Re: ROLLING 16 Months Growth

Thu, 27 Feb 2014 07:13:51 GMT

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.

Re: ROLLING 16 Months Growth

sunilkumarqv — Thu, 27 Feb 2014 07:17:59 GMT

Hi Himank,

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

You may get solution

Re: ROLLING 16 Months Growth

Thu, 27 Feb 2014 07:19:57 GMT

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

Re: ROLLING 16 Months Growth

Thu, 27 Feb 2014 07:22:37 GMT

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

Re: ROLLING 16 Months Growth

PrashantSangle — Thu, 27 Feb 2014 07:29:56 GMT

Hi,

if possible can you upload sample data with desired result

Re: Re: ROLLING 16 Months Growth

Thu, 27 Feb 2014 10:11:51 GMT

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

Re: Re: ROLLING 16 Months Growth

Thu, 27 Feb 2014 10:17:33 GMT

now share your qvw

Re: Re: Re: ROLLING 16 Months Growth

Thu, 27 Feb 2014 10:25:32 GMT

Here you GO

Re: Re: Re: ROLLING 16 Months Growth

Thu, 27 Feb 2014 10:31:11 GMT

i want to represent 16 month growth Trend.

How can I achieve that?

Please have a look on attached file.

Re: Re: Re: ROLLING 16 Months Growth

Thu, 27 Feb 2014 10:35:25 GMT

hi aamir

You are Calculating Growth wrt last month but i want to calculate Growth wrt last Year Same month.

Example (Oct 2013 -Oct 2012)/Oct 2012

Thanks

Re: ROLLING 16 Months Growth

PrashantSangle — Thu, 27 Feb 2014 11:13:57 GMT

Hi,

You can create a new variable

Year(date) as year

in script and nullify 'year' field instead of 'monthyear'.

Re: Re: Re: Re: ROLLING 16 Months Growth

Thu, 27 Feb 2014 11:16:19 GMT

Here it is

Re: ROLLING 16 Months Growth

Thu, 27 Feb 2014 16:17:35 GMT

Aamir again you are calculating growth wrt last month .

This is not my requirement.

I want Growth wrt Last Year Same Month

Re: ROLLING 16 Months Growth

PrashantSangle — Thu, 27 Feb 2014 16:38:42 GMT

Hi,

In xls data growth% is matching with your qvw if selection not made and when made selection values return 0

I think your struggling with nullify field.

If possible create Year , Month and Monthyear field at script level.

and you have to nullify year because while calculating data from last year you have to made it available,

this is all happening because of association.

Re: ROLLING 16 Months Growth

Thu, 27 Feb 2014 16:41:04 GMT

Thanks max

but can you give me an example with the existing Excel Attached?

Re: ROLLING 16 Months Growth

Thu, 27 Feb 2014 17:06:40 GMT

Hi,

If you have pivot table then you can use something like...

=RangeSum(sum([Sales]),-Before(sum([Sales])))/Before(sum([Sales]))

Vinay