topic Pivot total in QlikView

Pivot total

Anonymous — Tue, 07 Sep 2010 15:24:06 GMT

Hi all,

I have a table like below:

Problem is that the totals of Totaal (test) are not shown ... Totaal (test) has the following expression:

[Aantal] * [Kosten]

When selecting for instance only one Type the totals are shown correctly ... but for all Types it is not.

What am I missing here??

thx

Pivot total

Oleg_Troyansky — Tue, 07 Sep 2010 22:21:55 GMT

Most likely, both of your other expressions - "Aatal" and "Kosten", have conditions that work for a single Type but cannot be resolved when multiple types are available.

For example something like this:

If (Type = 'Budget', .... , ....)

THis type of expression can't be resolved at the total level. The solution is to use advanced aggregation and first calculate the values for each Type and then summarize them:

expr. = sum(aggr( <my current expression>, Type))

This should work...

cheers,

Pivot total

johnw — Wed, 08 Sep 2010 00:06:47 GMT

Might need to include all of the dimensions? Can't remember for sure. I guess if it doesn't work with just Type, add the others.

sum(aggr(<my current expression>,"Aankoop / Huur","SC","Type"))

Pivot total

Oleg_Troyansky — Wed, 08 Sep 2010 16:06:45 GMT

Good point, I missed the other dimensions - you definitely need to list all the dimensions in the AGGR.

Pivot total

rwunderlich — Wed, 08 Sep 2010 20:20:53 GMT

Try making your expression aggregatable:

=sum([Aantal] * [Kosten])

-Rob

Pivot total

Anonymous — Thu, 09 Sep 2010 07:29:17 GMT

I added this for both Aantal and Kosten ...

sum(aggr(<my current expression>,"Aankoop / Huur","SC","Type"))

Did try some stuff with aggr, but didn't came up this idea so thx.

Just having sum(<Aantal expression>) didn't work ... but with aggr it works great!

Only the problem now is that Kosten is also shown as total but shouldn't be a total ... but already working on that!!

So thanx you both for giving me a clue 😉