topic Re: Total Sum in Pivot Table in QlikView

Total Sum in Pivot Table

orital81 — Tue, 05 Nov 2013 13:17:43 GMT

I have an expression which has a different total in the pivot totals expression.

I would like to adjust the expression to fit pivot table total sum calculation.

The top table (straight) has a Correct Total Sum

the table below is a pivot which has a different sum

Expression:

If(Sum({<Proj_type = {'scc'}>} Sum_Expense)=0,sum({<Proj_type = {'mcc'}>}Sum_Expense),sum({<Proj_type = {'scc'}>}Sum_Expense))

See my attached example, user/pass: servit/servit123

Thanks

Re: Total Sum in Pivot Table

Anonymous — Tue, 05 Nov 2013 14:00:53 GMT

The straight table has total as "sum of rows". If you change it to "expression total", the result is the same as in pivot table.

In situations like this, in most cases the "Expression total" is a "more correct" result, if I may say so. For example, some data may be correctly taken into account in more than one row. If you use "sum of rows", you get it double-counted. "Expression total" eliminates double-counting.

Of course I am not aware about the specifics of your rules. Just explaining the difference.

Regards,

Michael

Re: Total Sum in Pivot Table

jonathandienst — Tue, 05 Nov 2013 14:11:46 GMT

To get a 'sum of rows' calculation in a pivot table, use an aggr like this:

Sum(Aggr(If(Sum({<Proj_type = {'scc'}>} Sum_Expense)=0,sum({<Proj_type = {'mcc'}>}Sum_Expense),sum({<Proj_type = {'scc'}>}Sum_Expense)), PivotDim1, PivotDim2,...))

Replace PivotDimn with all the pivot table dimensions, separated by commas.

HTH

Jonathan

Re: Total Sum in Pivot Table

orital81 — Tue, 05 Nov 2013 14:24:38 GMT

Thanks Jonathan, your suggestion works!

Re: Total Sum in Pivot Table

orital81 — Tue, 05 Nov 2013 14:25:08 GMT

Thanks Michael, this was helpful.