Calculating Cross-Dimensional Percentage Changes

Fri, 16 Sep 2011 16:19:13 GMT

Hi guys,

I'm working on a stacked chart displaying yearly revenue data.

So each bar represents the total revenue for a given year and is stacked/built from the revenues of composite businesses in that year. Within the graph, I want to calculate the year-on-year percentage change in total revenues.

I have tried using a code of the form:

=(sum({expression})-above(sum{expression}))/above(sum{expression}).

However, because the data is multidimensional, with "Year" being the larger dimension, the "above()" command doesn't work - instead it calculates the percentage difference between businesses.

Is there a way to construct the code so that qlikview overrides (or ignores) the business dimension for the purposes of this calculation only, or should I investigate changes in the expression I am using?

Thanks!!!

Re: Calculating Cross-Dimensional Percentage Changes

swuehl — Fri, 16 Sep 2011 17:30:44 GMT

Hi Qlikstarter,

that's quite a challenge for a start

You could maybe use

=aggr((sum(Sales)-above(sum(Sales)))/above(sum(Sales)),Group,Year)

to keep close to your approach using above function.

I think it is working (see attached sample), but using above, the results may depend on sort order.

And using the aggr-function around above, I think the order for above is determined by load order.

Probably there are some other approaches to solve your request, I leave it to others for today.

Hope this helps and have fun,

Stefan

topic Re: Calculating Cross-Dimensional Percentage Changes in QlikView

Calculating Cross-Dimensional Percentage Changes

Re: Calculating Cross-Dimensional Percentage Changes