topic Re: Pivot Table Sum in QlikView

Pivot Table Sum

mphekin12 — Mon, 02 Jun 2014 13:04:44 GMT

Can anyone please help out and tell me why the column titled 'Mix Builder' isn't summing correctly? Every other column in the pivot table is correct except for this one.

Thank you for your help!

Re: Pivot Table Sum

Jason_Michaelides — Mon, 02 Jun 2014 13:06:51 GMT

Are you expecting the total to be a sum of all the rows? That's not how pivot tables work - the total row is calculated independently over the grouped dataset. The result may well not be the same as the sum of all the rows.

Re: Pivot Table Sum

Mon, 02 Jun 2014 13:08:12 GMT

hard to say without explanations.

What should it show ?

What is the expression ?

Chris

Re: Pivot Table Sum

mphekin12 — Mon, 02 Jun 2014 13:16:18 GMT

Sorry for the confusion. Here is another screen shot with more information.

So if you look at Group 1 Sales, Group 1 Cost, Group 1 Profit, Group 2 Sales, Group 2 Cost, or Group 2 Profit at a division level, all of the Total lines are correct. If you look at the total line for Mix Builder it isn't. I guess my question is why does the total work over all columns of data but not the Mix Builder?

Thanks!

Re: Pivot Table Sum

Jason_Michaelides — Mon, 02 Jun 2014 13:17:31 GMT

What is the Mix Builder expression?

Re: Pivot Table Sum

mphekin12 — Mon, 02 Jun 2014 13:19:21 GMT

Jason,

Thanks for taking the time to look this over. Here is the formula:

(sum({$<Year=Group1::Year, Month=Group1::Month, Quarter=Group1::Quarter, Date=Group1::Date>} TOTAL<D.Country, D.Area, D.District, D.Division> Price_Ext)) *

(round(sum({$<Year=Group2::Year, Month=Group2::Month, Quarter=Group2::Quarter, Date=Group2::Date>} Price_Ext) / sum({$<Year=Group2::Year, Month=Group2::Month, Quarter=Group2::Quarter, Date=Group2::Date>} TOTAL<D.Country, D.Area, D.District, D.Division> Price_Ext), 0.00001)) *

(round(sum({$<Year=Group1::Year, Month=Group1::Month, Quarter=Group1::Quarter, Date=Group1::Date>} Profit) / sum({$<Year=Group1::Year, Month=Group1::Month, Quarter=Group1::Quarter, Date=Group1::Date>} Price_Ext), 0.00001))

Re: Pivot Table Sum

mphekin12 — Mon, 02 Jun 2014 13:21:17 GMT

Jason,

One more thing that may help answer this issue. I have a fast change on the object. When I view the data as a Straight table, the total is correct.

Thanks!

Re: Pivot Table Sum

Jason_Michaelides — Mon, 02 Jun 2014 13:25:23 GMT

How does this expression differ from the others?

Re: Pivot Table Sum

mphekin12 — Mon, 02 Jun 2014 13:29:47 GMT

This expression isn't much different from the others. The other columns actually build the Mix Builder column. When I look at each segment of the formula individually it works correctly, but as a whole the total doesn't match. I have verified that each individual line is correct, it is just the total that isn't.

Re: Pivot Table Sum

mphekin12 — Tue, 03 Jun 2014 18:46:03 GMT

After a lot of research (especially a post called 'Total Sum in Pivot Table') I figured out that I needed to wrap each segment of my formula in an aggr() function and then sum() the whole thing. For anyone interested the following formula gives me my desired outcome:

sum(

aggr((sum({$<Year=Group1::Year, Month=Group1::Month, Quarter=Group1::Quarter, Date=Group1::Date>} TOTAL<D.Country, D.Area, D.District, D.Division> Price_Ext)), D.Country, D.Area, D.District, D.Division, [Major Line])

(round(

aggr((sum({$<Year=Group2::Year, Month=Group2::Month, Quarter=Group2::Quarter, Date=Group2::Date>} Price_Ext)), D.Country, D.Area, D.District, D.Division, [Major Line])

aggr((sum({$<Year=Group2::Year, Month=Group2::Month, Quarter=Group2::Quarter, Date=Group2::Date>} TOTAL<D.Country, D.Area, D.District, D.Division> Price_Ext)), D.Country, D.Area, D.District, D.Division, [Major Line])

, 0.00001)

)

(round(

aggr((sum({$<Year=Group1::Year, Month=Group1::Month, Quarter=Group1::Quarter, Date=Group1::Date>} Profit)), D.Country, D.Area, D.District, D.Division, [Major Line])

aggr((sum({$<Year=Group1::Year, Month=Group1::Month, Quarter=Group1::Quarter, Date=Group1::Date>} Price_Ext)), D.Country, D.Area, D.District, D.Division, [Major Line])

, 0.00001)

)

Thanks to all that helped out!