Pivot Table Sum Aggregation based on Aggr Expressions

[billuran]

Hello All,

I have a pivot table with an expression that compares a Region's department's actual performance to a target and then from there creates a value of opportunity. Here is the expression.

(((Target-(sum({<[Exclude Hours]={'NO'},[Pay Type]={'Productive'}>} Hours)/Aggr(sum([Service Units]*Weight),Charged_BU,Charged_deptid)))*Aggr(sum([Service Units]*Weight),Charged_BU,Charged_deptid)/ppcount)*26/2080)))

Basically I cannot get this expression to total to the parent row of the pivot table. IE total each department's value to the appropriate region. It just shows -

Please let me know what other information i can provide.

[sunny_talwar]

Would you be able to share some screenshots to show where exactly are you having the issue and if possible share your qvw?

[billuran]

Sunny T, The FTE Variance is what I am after.

[sunny_talwar · Accepted solution]

Do you want this to be the sum of rows? For 110, do you want to see -0.1+0.9-0.4 = 0.4?

May be try like this:

Sum(Aggr(

(((Target-(sum({<[Exclude Hours]={'NO'},[Pay Type]={'Productive'}>} Hours)/Aggr(sum([Service Units]*Weight),Charged_BU,Charged_deptid)))*Aggr(sum([Service Units]*Weight),Charged_BU,Charged_deptid)/ppcount)*26/2080))),

Bus...DimensionHere, Department))

Unable to see the full name for Bus...Dimension, but replace that with the actual field name from your QlikView application

[billuran]

Worked!, Cheers!