Pivot Table Partial totals not working properly.

[nithin_miryala]

Hi Everyone.

In the below pivot table i have enabled partial totals for ID.

I want the total to be different sum of the Top rows. but it is defaulting to Expression total.

It is working the way it should Visits as the expression is Sum(Visits).

But when it comes to AMGA and MGMA there is some multiplication going on expression level. Rather than just adding the columns the total is also being calculated.

is there a way i restrict it to sum.

Thanks

Nithin

[sunny_talwar]

You need to use Sum(Aggr(, Dimension/s)) around your expression to fix the totals... read here

Sum of rows in pivot tables ‒ QlikView‌

Totals in Charts

Sum(Aggr(YourExpression, Division, ID))

[nithin_miryala]

This is really helpful i am 1 step closer to the answer.

My expression in AMGA is

SUM(AMGA) * Weight.  Weight is the dimension and weight is also calculated expression.

When i rewrite the expression in the following manner it is giving me the error.

sum( Aggr( sum(AMGA) * Weight , Division,ID) )

but when i take out the weight it is giving me correct format but i require weight to be multiplied

sum( Aggr( sum(AMGA) , Division,ID) )

How to write the multiplication inside the aggr()?

[Anil_Babu_Samineni]

Weight will return Row level, To work you may try this

sum( Aggr( sum(AMGA) * Sum(Weight) , Division,ID) )

OR

sum( Aggr( sum(AMGA* Weight) , Division,ID) )

[sunny_talwar · Accepted solution]

You can multiply within Aggr(), but you cannot use another column reference with Aggr(). If Weight is an expression, you need to write the whole expression again within the Aggr() function

Sum(Aggr(Sum(AMGA) * Expression4WeightHere, Division, ID))

[nithin_miryala]

That is also determined by PSR and FTE.

PSR*FTE/12.

i think i cant write this in side the aggr.

[sunny_talwar]

Anything which is not a field will not work inside Aggr(). If PSR and FTE are fields, then it will work

Sum(Aggr(Sum(AMGA) * PSR*FTE/12, Division, ID))

[nithin_miryala]

it throws an error if i write sum( Aggr( sum(AMGA* Weight) , Division,ID) ).

I tring to put all the stuff in the backend and do the aggr

[nithin_miryala]

ok. i will try to put all these in data modal and see

[Anil_Babu_Samineni]

Boom, You said title it "Pivot Table". We thought you are doing this in UI. And the concept Aggr() is not the existing function in Script (So, In script we are calling Group By like DB)

Anyway, Perhaps this?

Table:

Load Division, ID, AMGA, Weight From Source;

Load Division, ID, Sum(AMGA)*Sum(Weight) as AMGAWeight Resident Table Group By Division, ID;