Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Partner
Partner

Pivot Table Sum Aggregation based on Aggr Expressions

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.

1 Solution

Accepted Solutions

Re: Pivot Table Sum Aggregation based on Aggr Expressions

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

View solution in original post

4 Replies

Re: Pivot Table Sum Aggregation based on Aggr Expressions

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

Partner
Partner

Re: Pivot Table Sum Aggregation based on Aggr Expressions

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

Capture.PNG

Re: Pivot Table Sum Aggregation based on Aggr Expressions

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

View solution in original post

Partner
Partner

Re: Pivot Table Sum Aggregation based on Aggr Expressions

Worked!, Cheers!