Growth calculation in Pivot Chart - possible via Set Analysis?

Fri, 31 Jan 2014 10:44:45 GMT

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.

topic Growth calculation in Pivot Chart - possible via Set Analysis? in QlikView

Growth calculation in Pivot Chart - possible via Set Analysis?