topic Max function causing inaccurate totals in pivot/straight table in QlikView

Max function causing inaccurate totals in pivot/straight table

Tue, 24 May 2016 04:43:08 GMT

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?

Re: Max function causing inaccurate totals in pivot/straight table

chetansehgal — Tue, 24 May 2016 07:23:53 GMT

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

Re: Max function causing inaccurate totals in pivot/straight table

jonathandienst — Tue, 24 May 2016 07:26:01 GMT

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.

Re: Max function causing inaccurate totals in pivot/straight table

maniram23 — Tue, 24 May 2016 08:05:05 GMT

hi,

Please find the attached file.

Re: Max function causing inaccurate totals in pivot/straight table

Tue, 24 May 2016 08:39:29 GMT

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.

Re: Max function causing inaccurate totals in pivot/straight table

Tue, 24 May 2016 08:45:46 GMT

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.

Re: Max function causing inaccurate totals in pivot/straight table

Tue, 24 May 2016 08:49:16 GMT

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.

Re: Max function causing inaccurate totals in pivot/straight table

sunny_talwar — Tue, 24 May 2016 11:20:20 GMT

You can try this also:

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

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