topic Re: Aggr not working correctly with set analysis in QlikView

Aggr not working correctly with set analysis

Tue, 19 Mar 2013 12:05:11 GMT

I have an issue with Aggr and set analysis. Here is my situation:

I have a chart with 1 dimension, Payroll Month/Year (Monthname())

I have 2 expressions.

1: Gives possible values per selection. Meant to work where 1 employee is selected.

sum(

aggr(

if(

sum(if([Double Time]=0,[Labor Hours]))>=40

sum(if([Double Time]=0,[Labor Hours]))-40

)

[Employee ID],[Payroll Ending])

)

2: This is supposed to show all employees based on the the current selections possible department value. This is meant to show a comparison of employee vs department overtime values.

avg(

aggr(

if(

sum({<[JC Description]=P([JC Description]),[Employee]=>} if([Double Time]=0,[Labor Hours]))>=40

sum({<[JC Description]=P([JC Description]),[Employee]=>} if([Double Time]=0,[Labor Hours]))-40

)

,[Employee ID],[Payroll Ending])

)

My issue is that both formulas are returning the same value when 1 employee is selected.Can anyone tell what is wrong? This shows correctly without using aggr, but I need this to calculate per employee and per week to get the correct values.

Re: Aggr not working correctly with set analysis

Gysbert_Wassenaar — Tue, 19 Mar 2013 12:09:56 GMT

Your avg aggregates over [Employee ID],[Payroll Ending] taking current selections into account. So if you select one employee you get one result back since there's only one employee to calculate an aggregate for.

Maybe this does what you need:

avg({<Employee=>}

aggr(

if(

sum({<[JC Description]=P([JC Description]),[Employee]=>} if([Double Time]=0,[Labor Hours]))>=40

sum({<[JC Description]=P([JC Description]),[Employee]=>} if([Double Time]=0,[Labor Hours]))-40

)

,[Employee ID],[Payroll Ending])

)

Re: Aggr not working correctly with set analysis

Tue, 19 Mar 2013 12:13:19 GMT

Is there any way to use set analysis to exclude the current employee selection?

Re: Aggr not working correctly with set analysis

Tue, 19 Mar 2013 12:23:50 GMT

Prueba agregando "TOTAL" en AVG...

avg( TOTAL

aggr(

if(

sum({<[JC Description]=P([JC Description]),[Employee]=>} if([Double Time]=0,[Labor Hours]))>=40

sum({<[JC Description]=P([JC Description]),[Employee]=>} if([Double Time]=0,[Labor Hours]))-40

)

,[Employee ID],[Payroll Ending])

)

Re: Aggr not working correctly with set analysis

Tue, 19 Mar 2013 12:35:54 GMT

This did not work

Re: Aggr not working correctly with set analysis

Tue, 19 Mar 2013 12:48:05 GMT

ultima prueba

AVG(TOTAL

aggr(

if(

sum(if([Double Time]=0,[Labor Hours]))>=40

sum(if([Double Time]=0,[Labor Hours]))-40

)

[JC Description],[Payroll Ending])

)

Re: Aggr not working correctly with set analysis

Tue, 19 Mar 2013 12:53:50 GMT

That will not work either. It needs to be evaluated per employee due to the fact that overtime is calculated based on one person, not the total hours of a group. If 2 people work a total of 50 hours, 25 hours each, that is not overtime, which this expression would return 10.

Re: Aggr not working correctly with set analysis

Tue, 19 Mar 2013 15:32:00 GMT

I missed the expression you posted the first time around somehow haha. This is actually working, even though I clearly remember trying this. I must have done something wrong before.

Anyways thank you for your help