Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Growth calculation in Pivot Chart - possible via Set Analysis?

Hi everybody,

haven't found a clue in the usual resources, so I need to ask for help here:

I have a pivot chart where the periods (months or years) are displayed column-wise. I want to calculate growth versus previous year using the simple formula <Sales>/<Previous Year Sales> - 1. The calendar information are in a date field, normalized to the first of the respective month, as I need only monthly information. Date format is YYYY-MMM. Before() is not an option, as I want the growth information to be independent from the selection (a growth versus last year's December remains a growth vs. last year's December, even if last year's December is not selected)

My idea was using set analysis in a way like this:

=sum(VALUE_TSD) / sum({<FC_PERIOD = {"1(=Date(addDate(FC_PERIOD,-12),'YYYY-MMM'))"}>} VALUE_TSD) - 1

This doesn't work. It seems like the FC_PERIOD in curly brackets returns all possible values and thus makes the calculation fold. Matter of fact, it works fine, when I simply insert a string like "2013-Nov" or an aggregation like MAX(FC_PERIOD) which by definition returns but one value.

My question is: is there a way to pass the FC_PERIOD value which is related to the corresponding column to the formula? Or do I need to address all this in the data model?

Thanks in advance for all replies.

0 Replies