Sum of rows in pivot table

[Anonymous]

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?

[Lisa_P]

can you share your app or source data ?

[Anonymous]

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.

[Lisa_P]

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.

[Anonymous]

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?

[Anonymous]

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

[vishsaggi]

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.

[Anonymous]

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

[Anonymous]

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.

[Lisa_P]

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...