Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

ktwemlow
New Contributor III

Sum of rows in pivot table

I've searched in this forum and haven't found my exact issue raised, wondering if this is possible: I have a pivot table which can be expanded at various nodes by the user and a Target column which is calculated based on existing data (does some rounding). They decide what level each Target needs to be calculated at, by expanding or collapsing dimensions. But I need the total rows to show the total of the lower level rows, not recalculate with rounding, as currently totals do not add up.

In the chart below, the total for Company 1 (and Division 1) should be 151, not 155. I played around with dimensionality() and aggr() but the formula will be different depending on what nodes have been expanded. Anyone know how I can do this?

52 Replies
Employee
Employee

Re: Sum of rows in pivot table

can you share your app or source data ?

ktwemlow
New Contributor III

Re: Sum of rows in pivot table

Not easily, not without removing sensitive client data. Is there something specific in the data you need to see? I can try to explain it.

There is a 7 level hierarchy from Group down to Location and data is stored at the Location level. The formula is using a sum of a specific integer field, and then applying a percentage reduction, then rounding that result. Hence the number will be different at different levels due to the rounding.

Employee
Employee

Re: Sum of rows in pivot table

The rounding must be causing this issue.  We can only present the data as we see it.  Not sure how to get around this without having better granularity.

ktwemlow
New Contributor III

Re: Sum of rows in pivot table

I have a feeling this isn't possible. The formula is calculating correctly but I want the totals to just sum those rounded amounts, rather than recalculate. Not possible with a pivot table?

ktwemlow
New Contributor III

Re: Sum of rows in pivot table

Looking for the equivalent to 'sum of rows' from a straight table total.

vishsaggi
Esteemed Contributor III

Re: Sum of rows in pivot table

Just follow these instructions in below link to scramble sensitive data and upload.

Preparing examples for Upload - Reduction and Data Scrambling

Sample app would help us to look into which saves time.

ktwemlow
New Contributor III

Re: Sum of rows in pivot table

Thanks, I just thought maybe someone has done this, or can tell me that it's not possible.

ktwemlow
New Contributor III

Re: Sum of rows in pivot table

This is quite a complex app with a large data model and section access. If I get a chance tonight I'll mock up a simple app with an inline table with the levels and data as I've described above.

Employee
Employee

Re: Sum of rows in pivot table

Help does show the Aggr function as a solution.  Sound like you have already found that and it still doesn't work ?

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/ChartFunctions/NestedAgg...