topic Re: Additional help with totals in pivot table in QlikView

Additional help with totals in pivot table

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

Hi all,

I have a pivot table with a pick() dimension that uses a Group Name field and adds in a TOTAL. i have a number of expressions, most of the expressions show the correct total against the total line but there is 1 that don't and i'm unsure why.

the expression that i can't get to total correctly is 'Price Variance Impact', this should show a value of 41,906 against the group name of 'PNCO', the total however is showing 5682 when it should show the same as the 'PNCO' total.

is anyone able to help me understand why?

stalwar1‌ this is something you helped out with initially.

Re: Additional help with totals in pivot table

sunny_talwar — Wed, 14 Mar 2018 12:58:43 GMT

Change your variable to this

((sum(aggr(($(vSTDCostperCase)),Item,[Cust ID], Dim))-

sum(aggr(sum([YTD ACTUAL VALUE])/sum({<Period={"<$(=GetFieldSelections(Period))"}>}[Sales Volume]),Item, Dim)))

$(vTotalVolumetoLastPeriod))

(($(vSTDCostperCase)-$(vFutureCost))*$(vForecastSales))

Re: Additional help with totals in pivot table

hopkinsc — Wed, 14 Mar 2018 13:28:09 GMT

Thanks Sunny,

that works for the example i gave, but if i deselect the customer and product they dont match.

looking at each of the customers i can see quite a big difference between them all.

have i done something wrong?

Re: Additional help with totals in pivot table

hopkinsc — Wed, 14 Mar 2018 14:16:08 GMT

Hi Sunny,

also, i have just exported the table into excel and the total line for the 'Total' group doesnt add up to the sum of rows, i know in a pivot table the total mode is expression tatl, is there a way i can get the total rows instead?

or would i have to look at converting the pivot into a straight table?

Re: Additional help with totals in pivot table

hopkinsc — Wed, 14 Mar 2018 14:29:48 GMT

Hi Sunny, ignore the last 2 messages, i have fixed it by using

Sum({<Period>} Aggr(alt(sum(aggr($(vPriceVarImpact),Item,[Customer Name], Dim)),0), [Item Group Name], [Customer Name], Dim))

in the expression also.

this now works

thanks for your help!