Pivot Partial Sums Behavior

[#1]

In a Pivot Table, on your partial sums line, can you force Qlikview to simply sum the rows it is grouped on INSTEAD of the default behavior of calculating the sum based on aggregation rules.  For example see the attached file (would embed it into this post but keep on getting error).

See how the partial sum line shows $996.88 for last three rows?  Even though you can see that the last two rows have different numbers.  It's because QV is calculating the partial sum based on the partial sum totals NOT simply adding the total of each of those columns individually.  Hope that makes sense.

Can this be done in a Pivot Table?  I'm sure it might involve some weird trick like adding a row for 'Total' in your data but not sure.

Thanks!

[johnw]

In a pivot table, QlikView always applies your expression and never does a sum of rows.  But you're right that there's a weird trick to make it work.  You can set up your expression so that when evaluated as a total, the expression itself does a sum of rows.  The basic form of the expression is this:

sum(aggr(YourCurrentExpressionHere,Your,Chart,Dimensions,Here))

So if your chart looks like this:

Dimension 1 = Customer
Dimension 2 = Product
Expression  = max(OrderSize)

Then to get the partial sum to behave as a sum of rows, change your expression to this:

sum(aggr(max(OrderSize),Customer,Product))

[#3]

John thanks again for helping out.  Makes sense but unfortunetely I have some gnarly calculated dimensions in my chart dimension set.  In the aggr() function you only call fields right (I couldn't reproduce my gnarly functions ... even if I did it would be a performance nightmare).  Nor can I call the calculated dimensions using their label names ...  Any suggestions?  I hope creating my gnarly dimensions in the load script isn't my only option.T

Thanks again

[johnw]

Ah, yeah, it won't work with calculated dimensions.  Yet another reason I try to avoid calculated dimensions where at all possible.  I really do suggest creating your gnarly dimensions in the load script instead of in the chart if at all possible.  It will significantly boost chart performance (not that I know you have a performance problem to fix) and also solves the sum of rows problem. 

There might be another way to solve it with calculated dimensions, and as a hint you could check dimensionality() with an if() to put a completely different calculation on the total row, but I'm not thinking of what expression you could put there that would work.

[#5]

John - Well I bit the bullet and spent the entire day doing the right thing, recreating all of my crazy calculated dimensions into the data model.  Got that working.  So then I took your first suggestion and used the sum(aggr( trick so the QV evulates the expression it does a sum of rows.  I can get your example to work when I use a max() function but for some reason it's not working when I do a sum().

So I see this working:

sum(aggr(max(OrderSize),Customer,Product))

But not this:

sum(aggr(sum(OrderSize),Customer,Product))

In my scenario I am forced to use SET analysis with the embedded sum.  I am passing every dimension field.  Data is returning but again for some reason the partial sum is still just evaluating the partial sum row.  Any suggestions?  Have a good weekend

[johnw · Accepted solution]

Assuming your chart has dimensions Customer and Product, then this:

sum(aggr(sum(OrderSize),Customer,Product))

Is equivalent to this:

sum(OrderSize)

There is no difference here between doing a sum of rows and doing a sum of all order sizes.  Hopefully your real example is more complicated and has a reason to need sum of rows.

A possible issue is the set analysis.  For a set like {<Supplier={'Bob'}>}, you have to apply the same set to both aggregations for it to work properly:

sum({<Supplier={'Bob'}>} aggr(

max({<Supplier={'Bob'}>} OrderSize),Customer,Product))

[#7]

Hi John Witherspoon

your statement that sum(aggr(sum is the same as sum( is only correct if the summed field, and aggr conditions are on the same table.

I have an instance of exactly this issue where I am summing a field on the 'members' table based on when their purchase happened. If I AGGR by month (of the sale), it's possible that I get a member in multiple months making purchases, so they would count/sum multiple times. By re-writing my calculations as a SUM(AGGR(SUM( I do get a total of rows in my pivot table.

Thanks for the solution!

[sibrulotte]

I've been trying to get this principle to work and I am not getting there...

here is my set analysis up to now, but it doesn't work.

(sum({1< VAL_RPT.VAL_DATE = p(Date_calcul), VAL_RPT.PORT_NO = {$(vR_PAI_00230_PORT_NO)} >} aggr(VAL_RPT.MKT_VALUE, VAL_RPT.PORT_CODE, VAL_RPT.CUSIP)) +
sum({1< VAL_RPT.VAL_DATE = p(Date_calcul),  VAL_RPT.PORT_NO ={$(vR_PAI_00230_PORT_NO)}>} aggr(VAL_RPT.ACCRUAL_AMMOUNT, VAL_RPT.PORT_CODE, VAL_RPT.CUSIP)) )

/

(sum({1< VAL_RPT.VAL_DATE = p(Date_calcul), VAL_RPT.PORT_NO = {$(vR_PAI_00230_PORT_NO)} >} total aggr(VAL_RPT.MKT_VALUE, VAL_RPT.PORT_CODE, VAL_RPT.CUSIP)) +

sum({1< VAL_RPT.VAL_DATE = p(Date_calcul),  VAL_RPT.PORT_NO ={$(vR_PAI_00230_PORT_NO)}>} total aggr(VAL_RPT.ACCRUAL_AMMOUNT, VAL_RPT.PORT_CODE, VAL_RPT.CUSIP)) )

*

sum({1< VAL_RPT.VAL_DATE = p(Date_calcul), VAL_RPT.PORT_NO = {$(vR_PAI_00230_PORT_NO)} >} aggr(VAL_RPT.RANK,VAL_RPT.PORT_CODE, VAL_RPT.CUSIP) )

So I am adding, mkt_values to accrual ammounts, getting the total for the same addition, to get a %, then multiplying by the RANK.

I'm almost abandonning and using a value calculating by script...

[#9]

Hi Simon,

I'm having trouble understanding what your aggr() functions are doing. AGGR evaluates an expression based on a dimension.

So, I would expect:

aggr(SUM(VAL_RPT.ACCRUAL_AMMOUNT), VAL_RPT.PORT_CODE, VAL_RPT.CUSIP)

I'm not sure if that's going to totally solve your issue though, but as i see it, your aggr functions are meaningless.