Can anyone tell me why TotalMode is unavailable for Pivot Table.

[nikhilgarg]

Hey,

Can anyone tell me why TotalMode is unavailable for Pivot Table.

Thanks

[gautik92]

you will have subtotals  in pivot table

[malini_qlikview]

Hi Nikhil,

In Pivot Table the total Mode will be diasbled in Expressions tab, rather you can make use of the Subtotals in Presentation tab

[#4]

by nature pivot tables are a little different than straight tables, and as such, the option is disabled.  The corresponding option is to use the 'Show Partial Sums' on the Presentation tab

[hic]

The pivot table and the straight table have different calculation algorithms internally (to accommodate for the grouping and the sorting features), and the "Total mode" feature is very hard to implement in the pivot table algorithm.

However, you can achieve the same thing by using an Aggr() function. The "Sum of Rows" calculation can be defined by

  Sum(Aggr( <OriginalPivotExpression>, <PivotDimension> ))

HIC

[nikhilgarg]

Hey Thanks but can you also tell me difference between total expression and sum of rows with any example?

Thanks

[hic]

Expression total is the "correct" one - it evaluates the expression, but for the entire possible data sample. The Sum of Rows is sometimes (but not always) different.

In the following example you have sales per product. And one product can belong to two product groups. Note the difference in the totals.

In this example, the Sum of Rows is obviously wrong, but there are other examples where it is correct to sum over the individual rows.

HIC

[nikhilgarg]

Hey,

Thanks HIC.But i just did a small implementation that you did above and get different result in Expression Total. Can you plz explain why is it so.

[hic]

Of course you get a different result. You have all the numbers in one single source table - and in this table the three rows sum up to 500. The aggregation always sums in the relevant source table.

HIC