topic Growth% Sales calculation with time dimension in QlikView

Growth% Sales calculation with time dimension

Wed, 03 Nov 2010 17:12:01 GMT

Hi!, I want to calculate the growht% of sales between months or quarters or years in a pivot table.

(SalesFromPreviousSelectionTimeDimension-SalesFromCurrentTimeDimension)/SalesFromCurrenttimeDimension

I've seen the use of Set Analysis to apply this, but my question is if I can use a time cycle group that I created to dynamicaly calculate the growth depending on the level heriarchy

My time cycle group is a standard one called Time:

-Year

-Quarter

-Month

My question if somehow I can come up with an expression that uses this group in the pivot table

((${<Time=-1>}Sales-${<Time>})Sales)/${<Time>}Sales

Growth% Sales calculation with time dimension

johnw — Wed, 03 Nov 2010 22:52:08 GMT

Let's start by simplifying a bit. (A-B)/B = A/B - 1, so we won't have to repeat the expression for B. Then honestly, I'd probably write a separate "expression" for each field in the group, because I'm not thinking of any way to do Time-1 other than separating it out. Are they selecting a specific Year, Quarter or Month? Assuming they are, maybe something like this:

pick(match(getcurrentfield(Time),'Year','Quarter','Month')
,sum({<Year={'$(=Year-1)'}>} Sales)
,sum({<Quarter={ however you define the previous quarter }>} Sales)
,sum({<Month={ however you define the previous month }>} Sales))
/sum(Sales) - 1

Growth% Sales calculation with time dimension

Thu, 04 Nov 2010 00:22:08 GMT

Thank you!,

I also stumbled upon the "before" function that helps me calculate this too!

Growth% Sales calculation with time dimension

Tue, 28 Jun 2011 18:47:41 GMT

Thanks for referencing the 'Before' function - it works very cleanly in a less complex pivot table

by adding the expression

Growth%=Sum(Sales)/Before(sum(Sales))-1