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.
