topic Re: PivotTable (Dynamic Table) subtotal is wrong in QlikView

PivotTable (Dynamic Table) subtotal is wrong

luiz_damascena — Wed, 25 Nov 2020 16:16:04 GMT

I have a dynamic table with three dimensions (country, state, city) and three expressions (sales, deliveries, pending).

The expression "pending" is a formula: if (deliveries> sales, 0, Ceil ((sales - deliveries), 1))

By hypothesis, three lines;

in the first, pending = 10;

in the second, pending = 0 (deliveries are greater than sales, without the formula, the result would be -5);

in the third, pending = 20.

Subtotal display is enabled; the sum should be 30 (10 + 0 + 20). But he checks 25 (10 - 5 + 20). Why?

I tried some combinations using AGGR in the formula, but I did not succeed.

Thanks in advance.

Re: PivotTable (Dynamic Table) subtotal is wrong

sunny_talwar — Fri, 23 Feb 2018 13:02:38 GMT

Try this

Sum(Aggr(If(Expression4DeliveriesHere > Expression4SalesHere, 0, Ceil((Expression4SalesHere- Expression4DeliveriesHere), 1)), country, state, city))

Re: PivotTable (Dynamic Table) subtotal is wrong

hic — Fri, 23 Feb 2018 13:20:16 GMT

First of all, the expression

if (deliveries> sales, 0, Ceil ((sales - deliveries), 1))

contains several naked field references, and these can often not be used in your measures. You need to wrap all field references in an aggregation function like Sum(). I.e. you need to decide whether to aggregate first, and then use the condition in the If(), or if you want to use the condition in the If() on each data record, and then aggregate. See more on Use Aggregation Functions!

Further, the subtotal is not necessarily the sum of rows - and it shouldn't be. See Totals in Charts

Finally, if you choose a solution with Aggr(), the structure should be

Sum(Aggr(Sum(...), ... ))

Note that you should have one aggregation function outside the Aggr() and a second inside the Aggr().

HIC

Re: PivotTable (Dynamic Table) subtotal is wrong

luiz_damascena — Fri, 23 Feb 2018 13:26:51 GMT

It worked perfectly!

Not being able to use the names of the expressions, and having to repeat all the content of the same ones in the formula, is annoying ...

But thanks, the solution worked perfectly.

Re: PivotTable (Dynamic Table) subtotal is wrong

luiz_damascena — Fri, 23 Feb 2018 13:28:34 GMT

Thanks! the links are of excellent material.

Re: PivotTable (Dynamic Table) subtotal is wrong

sunny_talwar — Fri, 23 Feb 2018 13:30:29 GMT

You can use variables if you would not like to repeat the expressions over and over again, but column labels don't really work within Aggr() function.