Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Growth% Sales calculation with time dimension

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

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

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

View solution in original post

3 Replies
johnw
Champion III
Champion III

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

Not applicable
Author

Thank you!,

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

Not applicable
Author

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