Max function causing inaccurate totals in pivot/straight table

[#1]

Look at the attached application. Where max(sales) or max with any field is used the subtotals and totals are just the max values of each dimension and not total of all dimension values. Sum() calculates properly but this is just a sample app and in my application I have to use max function. I also used aggr with max and that solved the problem but as I said this is just a sample app. In the actual app there are lots of inline dimensions and if i used Aggr then then I think I would need to use Pick(Dimensionality(), expr1, expr2...)

which will lead to lots of coding in many places where the max function is used and also performance reduction. So is there any other solution for this?

[chetansehgal]

Hi Mohammed,

Your question is bit confusing.Can you please tell what exactly you want in the subtotals.

You need Max value among the expanded rows or you need the sum of all the max values in the expanded rows.?

Thanks,

Chetan

[jonathandienst · Accepted solution]

I don't of think Aggr() as a "work around" - it is the regular way to calculate "sum of rows total" for the totals in a pivot table, rather than the default "expression total".

>>...I think I would need to use Pick(Dimensionality(), expr1, expr2...)

Not sure why you think that. The Aggr() function needs to include all the chart dimensions. No conditional is necessary for the different subtotal levels.

[maniram23]

hi,

Please find the attached file.

[#5]

Thanks for trying out. But you see that is what is happening. See the overall totals in the last row is not the same as

sum(Sales), aggr(Expr.). I want the overall totals to be the same in case of max function.

[#6]

Oh you are right, if I use the aggr() function with all dimensions then the results stays the same as sum().

I thought of using Pick(Dimensionality() ) because I was under the notion that if I collapsed some dimensions then the aggr(max()) would give me incorrect results again but it doesn't. I am a newbie (2.5 months to be accurate) to Qlikview and still learning. Thank you I will try the aggr expression with all dimensions.

[#7]

Actually the max function is applied over a field at the backend, its not related to front end although I can't tell you exactly since I am just studying the app and helping in some minor changes.

[sunny_talwar]

You can try this also:

=Pick(Dimensionality()+1, Sum(Sales), Sum(Sales), Max(Sales))

or

=If(Dimensionality() < 2, Sum(Sales), Max(Sales))