topic Re: Aggregated Percentage Calculation in QlikView

Aggregated Percentage Calculation

Wed, 11 Dec 2013 08:14:53 GMT

I have a table containing two dimensions (week, ressource).

Furthermore I have two key figures (capacity, planned).

Now I need to set up a chart showing the ratio of planned to the capacity.

This means I have to devide sum(planned) through the sum of the capacity for each week.

But thats not correct at all.

The capacity must be the sum of all ressources for each week.

I have tried to use this expression =SUM(AGGR(sum(capacity),WEEK)) and the result looks like this:

The values are correct but the capacity must be shown in all rows.

How can I do this?

Anonymous — Wed, 11 Dec 2013 16:16:59 GMT

can you post a sample app.. that way it will be easier to try different things and suggest the working option.

hic — Wed, 11 Dec 2013 16:25:16 GMT

You should do almost the same, but your expression should be

RangeSum(Above(Sum(planned), 0, Count([VGFRSNAME.Resourcename]))) / Sum(total <CW> capacity)

or something similar.

HIC

Wed, 11 Dec 2013 16:27:49 GMT

I found the solution on my own. But thanks for your reply Aadil.

Its much easier than I thought.

Just use SUM(TOTAL <[WEEK]> capacity).

The expression will look like than:

=SUM(planned)/(SUM(TOTAL <[week]> capacity))*100