topic Expression Total is erratic in Pivot Table, while Straight Table is OK in QlikView

Expression Total is erratic in Pivot Table, while Straight Table is OK

atsushi_saijo — Tue, 15 Apr 2014 13:22:10 GMT

Dear Gurus,

I have a strange phenomena in QV. While expression results in correct result, the total sum does not result in the correct summation per individual lines.

In expression, we have such expression.

if(count({<DocDate={">=$(=MonthStart(Today(0),$(=-Y)))<=$(=MonthEnd(Today(0),$(=-Y)))"}>} DocDate)>0 ,sum(Weight) )

Y is an input value. Initialised as 1.

The result should be 818, but it indicates in 7,463 in Pivot Table.

If you possibly open the attached file, upper is straight table, with total mode 'Sum of Rows'. This results in 818. While Pivot Table result in 7565. In fact, if we export into Excel, we only have 818!

*Since this is only on of the columns in a report in the production release, Pivot table is mandatory.

I'd appreciate for your support.

Re: Expression Total is erratic in Pivot Table, while Straight Table is OK

cfz — Tue, 15 Apr 2014 13:38:27 GMT

Hi Atsushi ,

This a normal behavior, in a pivot table the total is calculated always as an expression total and not as a sum of rows What you have to do to get the same result as in the straight table is to modify the expression to incorporate the sum on it like in your modified document attached.

I hope it helps.

Carlos

Re: Expression Total is erratic in Pivot Table, while Straight Table is OK

atsushi_saijo — Tue, 15 Apr 2014 13:44:01 GMT

Dear Carlos,

I appreciate for the correction. Yes this is the desired result.

sum(aggr(if(count({<DocDate = {">=$(=MonthStart(Today(0),$(=-X)))<=$(=MonthEnd(Today(0),$(=-X)))"}>} DocDate)>0 , sum(Weight) ),ProductSerial))

Atsushi