[ASK] Different result with total sum of rows

[rendiyan]

hello qlikview developers,

i have an issue i want to ask.

so i have a straight table with dimension A, B, and ABC expression.

My expression formula is :

If(sum(A)/sum(B)>1,1,sum(A)/sum(B))* sum(C)

Also i give total of rows sum in my straight table.

And then i create a pivot table with same dimension and expression as above.

Here's when the strange thing happen.

When i expand all dimension, i got the same row result.

But when i only expand for dimension A, i got the different result from the total of rows sum in my straight table.

Is there any explanation why and how to solve this?

Thanks a lot before,

Best Regards

[swuehl · Accepted solution]

A pivot table is always using Expression Total as totaling mode. You need to use advanced aggregation for a sum-of-rows total in a pivot table:

=Sum( Aggr( If(sum(A)/sum(B)>1,1,sum(A)/sum(B))* sum(C), A,B,ABC))

[jonathandienst]

>>Is there any explanation why and how to solve this?

Because sum of rows is a "sum of the ratios", while the expression total is a "ratio of the sums", and these are arithmetically different values.

As swuehl already stated you need a sum(aggr()) structure to get a sum of rows result in a pivot table.