topic Re: Pivot Table Partial totals not working properly. in QlikView

Pivot Table Partial totals not working properly.

nithin_miryala — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Pivot Table Partial totals not working properly.

sunny_talwar — Wed, 27 Dec 2017 15:07:09 GMT

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

Re: Pivot Table Partial totals not working properly.

nithin_miryala — Wed, 27 Dec 2017 15:46:40 GMT

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

Re: Pivot Table Partial totals not working properly.

Anil_Babu_Samineni — Wed, 27 Dec 2017 15:49:15 GMT

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

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

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

Re: Pivot Table Partial totals not working properly.

sunny_talwar — Wed, 27 Dec 2017 15:49:34 GMT

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

Re: Pivot Table Partial totals not working properly.

nithin_miryala — Wed, 27 Dec 2017 15:58:26 GMT

That is also determined by PSR and FTE.

PSR*FTE/12.

i think i cant write this in side the aggr.

Re: Pivot Table Partial totals not working properly.

sunny_talwar — Wed, 27 Dec 2017 16:00:15 GMT

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

Re: Pivot Table Partial totals not working properly.

nithin_miryala — Wed, 27 Dec 2017 16:01:00 GMT

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

Re: Pivot Table Partial totals not working properly.

nithin_miryala — Wed, 27 Dec 2017 16:02:03 GMT

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

Re: Pivot Table Partial totals not working properly.

Anil_Babu_Samineni — Wed, 27 Dec 2017 16:04:44 GMT

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;

Re: Pivot Table Partial totals not working properly.

sunny_talwar — Wed, 27 Dec 2017 16:06:03 GMT

Aggr() function is for the front end of the app... doesn't work in the script....

Re: Pivot Table Partial totals not working properly.

nithin_miryala — Wed, 27 Dec 2017 16:26:51 GMT

I was referring to all the other feilds