Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Do More with Qlik - for Beginners and Beyond, Topic: Qlik Replicate on January 21, 2PM EST. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Partner
Partner

Additional help with totals in pivot table

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.

1 Solution

Accepted Solutions

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

View solution in original post

4 Replies

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

View solution in original post

Partner
Partner

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?

Partner
Partner

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?

Capture.JPG

Partner
Partner

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!