Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
nithin_miryala
Creator
Creator

Pivot Table Partial totals not working properly.

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.

Capture.JPG

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

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

11 Replies
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
Creator
Creator
Author

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

My expression in AMGA is

SUM(AMGA) * WeightWeight 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) )



Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
sunny_talwar

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
Creator
Creator
Author

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
Creator
Creator
Author

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
Creator
Creator
Author

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;

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful