topic Pivot Table totals and negative expression values in QlikView

Pivot Table totals and negative expression values

Tue, 04 May 2010 13:35:26 GMT

... do they work?

I've got a Pivot Table with an expression that can produce negative values. The negative values are displayed correctly. The totals however seem to stop working whenever there is a negative value to include, all I get is "-". Totals over only positive values (including 0) work fine.

The expression is something like = Count(x) * y, where y can be 1, 0 or -1.

What am I doing wrong here?

Edit - just tested this with another expression: = Sum(z), where z can be different values (negative as well as positive). And behold: The totals work! Will do some more testing...

Second edit - substituted the first expression = Count(x) * y with = Sum(y) which produces the same result plus working totals (and is more elegant even). But I still think the first expression should have worked also.

Pivot Table totals and negative expression values

johnw — Tue, 04 May 2010 18:33:56 GMT

I suspect the problem is that the pivot table is applying your expression again for the total, but at that point, Y has multiple values, so it can't do the multiplication, so it returns null, displayed as '-'.

Sum(Y) works because it puts Y INSIDE of the aggregation, and thus each ROW only has one value, so it can do the calculation.

If that wasn't an option, you could have used dimensionality() to detect whether you're on that particular subtotal, and substitute the expression you want. Something along these lines for a one-dimensional pivot table with a grand total:

if(dimensionality(),count(X)*Y,sum(aggr(count(X)*Y,MyDimension)))

Pivot Table totals and negative expression values

Oleg_Troyansky — Tue, 04 May 2010 20:18:52 GMT

In most cases (with very few exotic exceptions) the following formula should work just fine:

sum(X*Y)