Issue with Partial Sums

[Anonymous]

I have an invoicing report that comprehends the years of 2016 and 2017, and my goal is to check the average price change I had from 2016 to 2017.

I'm using the following Dimensions in a Pivot Table:

Division

Customer

Item

The first expression I used is to sum only how many items were sold that only appear in both years, so it is as follows:

Qty 2016:

if( sum ( {<Year={'2017'}>} Qty ) >0  and sum( {<Year={'2016'}>} Qty ) >0 , sum( {<Year={'2016'}>} Qty ))

Qty 2017:

if( sum ( {<Year={'2017'}>} Qty ) >0  and sum( {<Year={'2016'}>} Qty ) >0 , sum( {<Year={'2017'}>} Qty ))

Which worked perfectly, and I did the same to find how much was the average unit price that I sold for, which is the following:

Avg. Price 2016:

if( sum ({<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0 , sum( {<Year={'2016'}>} Total Invoiced ))
/ if( sum ({<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty )>0 ,sum( {<Year={'2016'}>} Qty))

Avg. Price 2017:

if( sum ({<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0 , sum( {<Year={'2017'}>} Total Invoiced ))
/ if( sum ({<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty )>0 ,sum( {<Year={'2017'}>} Qty))

And finally to compare both years I used three expressions, one to find the total invoiced on 2017, another to multiply the qty sold 2017 by the average price of 2016 and finally one to check the percetage that increased from one to the other:

Total 2017:

if( sum ({<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0 ,sum( {<Year={'2017'}>} Total Invoiced ))

Compare 2016:

(if( sum ({<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>}Qty) >0 , sum( {<Year={'2016'}>} Total Invoiced ))
/ if( sum({<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0 , sum( {<Year={'2016'}>} Qty )))
* (if( sum( {<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0 , sum( {<Year={'2017'}>} Qty )))

% Price Change:

((if (sum ({<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0 , sum( {<Year={'2017'}>} Total Invoiced )))
/ ((if( sum( {<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0 , sum( {<Year={'2016'}>} Total Invoiced ))
/ if( sum( {<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0 , sum( {<Year={'2016'}>} Qty )))
* (if( sum( {<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0 , sum( {<Year={'2017'}>} Qty ))))) -1

The final report is something like this:

The issue is when I go to the Chart Properties under the Presentations tab and select to Show Partial Sums to the DIVISION dimension, for example, it is not showing a straigth sum of the columns, but is actually showing a number that has nothing to do with anything. I only found that out after migrating the chart to Excel.

Any idea of how can I fix this? (Sorry for the long expressions, if you have a solutions to shorten it I'll appreciate a lot as well!)

[juraj_misina]

Marcelo,

there are two options:

Number one, you can switch to straight table, then go to table properties -> expressions, select the Price Change expression and change total mode to "sum Of Rows", then go to dimension limits tab and check "Show total" for your dimensions.

Number two is a bit trickier. If you need to have pivot table, then you need to change your expressions and use Aggr() function to calculate desired expression total (there's a difference between sum of rows and expression total, more info here: Totals in Charts). The resulting expression might look somewhat like this:

Sum(Aggr(If(sum ({<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0,  
sum( {<Year={'2017'}>} Total Invoiced )/((sum( {<Year={'2016'}>} Total Invoiced )/sum( {<Year={'2016'}>} Qty )*sum( {<Year={'2017'}>} Qty )))
), DIVISION, CUSTOMER, ITEM))-1

By the way, since the condition is always the same, you can simplify most of your expressions using only one if statement.

Good luck!

[Anonymous]

Juraj,

I used the following expression for the Total 2017 and it worked perfectly:

Sum (Aggr (if (sum ({<Year={'2017'}>} QTY) >0 and sum( {<Year={'2016'}>} QTY) >0 ,
sum({<Year={'2017'}>} TOTAL_INVOICED )), DIVISION, CUSTOMER_GROUP, SUB_DIVISION, CUSTOMER_CODE, ITEM))

But when I try to compare with 2016 using 2017 quantities it's working only on each individual cell, but the total is still not a sum

(((Sum (Aggr (if (sum ( {<Year={'2017'}>} QTY) >0 and sum ( {<Year={'2016'}>} QTY) >0 ,
sum ( {<Year={'2016'}>} TOTAL_INVOICED)), DIVISION, CUSTOMER_GROUP, SUB_DIVISION, CUSTOMER_CODE, ITEM)))
 / (Sum (Aggr (if (sum ( {<Year={'2017'}>} QTY) >0 and sum( {<Year={'2016'}>} QTY) >0 ,
sum( {<Year={'2016'}>} QTY)), DIVISION, CUSTOMER_GROUP, SUB_DIVISION, CUSTOMER_CODE, ITEM))))
 * (Sum (Aggr (if (sum( {<Year={'2017'}>} QTY) >0 and sum( {<Year={'2016'}>} QTY )>0 ,
sum( {<Year={'2017'}>} QTY)), DIVISION, CUSTOMER_GROUP, SUB_DIVISION, CUSTOMER_CODE, ITEM))))

And for the percentage change the same thing is happening, only each individual line is calculating correctly, but the bottom total line isn't.

(Sum(Aggr(if(sum({<Year={'2017'}>} QTY)>0 and sum({<Year={'2016'}>} QTY)>0 ,
sum({<Year={'2017'}>} TOTAL_INVOICED )), DIVISION, CUSTOMER_GROUP, SUB_DIVISION, CUSTOMER_CODE, ITEM)))
/
(((Sum (Aggr (if (sum ({<Year={'2017'}>}  QTY) >0 and sum ({<Year={'2016'}>}  QTY) >0 ,
sum ({<Year={'2016'}>} TOTAL_INVOICED)), DIVISION, CUSTOMER_GROUP, SUB_DIVISION, CUSTOMER_CODE, ITEM)))
 / (Sum (Aggr(if(sum({<Year={'2017'}>}  QTY) >0 and sum({<Year={'2016'}>}  QTY) >0 ,
 sum({<Year={'2016'}>}  QTY)), DIVISION, CUSTOMER_GROUP, SUB_DIVISION, CUSTOMER_CODE, ITEM))))
 * (Sum (Aggr(if(sum({<Year={'2017'}>}  QTY) >0 and sum({<Year={'2016'}>}  QTY )>0 ,
 sum({<Year={'2017'}>}  QTY)), DIVISION, CUSTOMER_GROUP, SUB_DIVISION, CUSTOMER_CODE, ITEM))))
 -1

Also, how do I simplify my expressions using only one if statement?

Thanks!

[juraj_misina]

Hello Marcelo,

you certainly do not need to use Aggr() for Qty 2016, Qty 2017, Total 2017. Those three expressions were perfectly alright. Avg 2016 and Avg 2017 do not need Aggr neither, but can be simplified by using only one if statement:

//2016
if( sum ({<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0 , sum( {<Year={'2016'}>} Total Invoiced )/sum( {<Year={'2016'}>} Qty)) 
//2017
if( sum ({<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0 , sum( {<Year={'2017'}>} Total Invoiced )/sum( {<Year={'2017'}>} Qty))  

Compare 2016 vs 2017 should probably be:

Sum(Aggr(If(sum ({<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0,    
((sum( {<Year={'2016'}>} Total Invoiced )/sum( {<Year={'2016'}>} Qty )*sum( {<Year={'2017'}>} Qty )))  
), DIVISION, CUSTOMER, ITEM))

And percentage change should work in a way I wrote before (at least I hope so 😞

Sum(Aggr(If(sum ({<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0,    
sum( {<Year={'2017'}>} Total Invoiced )/((sum( {<Year={'2016'}>} Total Invoiced )/sum( {<Year={'2016'}>} Qty )*sum( {<Year={'2017'}>} Qty )))  
), DIVISION, CUSTOMER, ITEM))-1

Let me know if this helped. If not, try uploading a sample application, maybe I can sort it out for you.

J

[Anonymous]

Hello Juraj!

For the Total 2017 I used the Aggr() function because without it the subtotal of the partial sum of the column was not working properly.

Follow an example, I added the expression for percentage change that I did, which is working on a row level, but not on the subtotal, and the one you sugested, that is working on a row level, but is also showing what is not found on both years, and I wanted to see only what was sold on both years (as the pictures below).

My expression

Your sugstion

[juraj_misina · Accepted solution]

Hello Marcelo,

this one is a fun one. Check the attached file, I added a new sheet where I hope I used correct expressions for your case. I created two options for the last one (% Diff), one calculates overall % difference between the years (which makes sense to me), the other calculates % difference on individual rows and then sum of rows on total row (which was your original requirement I think).

[Anonymous]

Juraj,

It finally worked! Though I had to change the Compare 2016 field, on your file the expression was:

sum({<Year={'2016'}>} If(Aggr(NODISTINCT sum({<Year={'2017'}>}QTY), DIVISION, [CUSTOMER GROUP], SUBDIVISION, [CUSTOMER CODE], ITEM)>0 
and Aggr(NODISTINCT sum({<Year={'2016'}>}QTY), DIVISION, [CUSTOMER GROUP], SUBDIVISION, [CUSTOMER CODE], ITEM)>0,  [TOTAL INVOICED]))
/
sum({<Year={'2016'}>} If(Aggr(NODISTINCT sum({<Year={'2017'}>}QTY), DIVISION, [CUSTOMER GROUP], SUBDIVISION, [CUSTOMER CODE], ITEM)>0 
and Aggr(NODISTINCT sum({<Year={'2016'}>}QTY), DIVISION, [CUSTOMER GROUP], SUBDIVISION, [CUSTOMER CODE], ITEM)>0,  QTY))
*
sum({<Year={'2017'}>} If(Aggr(NODISTINCT sum({<Year={'2017'}>}QTY), DIVISION, [CUSTOMER GROUP], SUBDIVISION, [CUSTOMER CODE], ITEM)>0 
and Aggr(NODISTINCT sum({<Year={'2016'}>}QTY), DIVISION, [CUSTOMER GROUP], SUBDIVISION, [CUSTOMER CODE], ITEM)>0,  QTY))

What I did was add the Aggr() to embrace everything so it looked like this:

Sum(Aggr(sum({<Year={'2016'}>} If(Aggr(NODISTINCT sum({<Year={'2017'}>}QTY), DIVISION, [CUSTOMER GROUP], SUBDIVISION, [CUSTOMER CODE], ITEM)>0 
and Aggr(NODISTINCT sum({<Year={'2016'}>}QTY), DIVISION, [CUSTOMER GROUP], SUBDIVISION, [CUSTOMER CODE], ITEM)>0,  [TOTAL INVOICED]))
/
sum({<Year={'2016'}>} If(Aggr(NODISTINCT sum({<Year={'2017'}>}QTY), DIVISION, [CUSTOMER GROUP], SUBDIVISION, [CUSTOMER CODE], ITEM)>0 
and Aggr(NODISTINCT sum({<Year={'2016'}>}QTY), DIVISION, [CUSTOMER GROUP], SUBDIVISION, [CUSTOMER CODE], ITEM)>0,  QTY))
*
sum({<Year={'2017'}>} If(Aggr(NODISTINCT sum({<Year={'2017'}>}QTY), DIVISION, [CUSTOMER GROUP], SUBDIVISION, [CUSTOMER CODE], ITEM)>0 
and Aggr(NODISTINCT sum({<Year={'2016'}>}QTY), DIVISION, [CUSTOMER GROUP], SUBDIVISION, [CUSTOMER CODE], ITEM)>0,  QTY)),
DIVISION,[CUSTOMER GROUP], SUBDIVISION, [CUSTOMER CODE], ITEM))

Now it's working perfectly, thanks a lot for your help!

[juraj_misina]

Hello Marcelo,

glad to hear it worked. One word of warning though, this is not the best approach performance-wise. If you plan to use this with large dataset or complex data model, try to create a flag for those items which meet the condition. Thus you would be able to use that flag in calculation or set analysis and dramatically simplify those expressions.

Best

Juraj