7 Replies Latest reply: Mar 19, 2013 11:32 AM by Marc Livingston

Aggr not working correctly with set analysis

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

,

0

)

,

[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

,0

)

,[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

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

,0

)

,[Employee ID],[Payroll Ending])

)

• Re: Aggr not working correctly with set analysis

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

• Re: Aggr not working correctly with set analysis

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

• Re: Aggr not working correctly with set analysis

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

,0

)

,[Employee ID],[Payroll Ending])

)

• Re: Aggr not working correctly with set analysis

This did not work

• Re: Aggr not working correctly with set analysis

ultima  prueba

AVG(TOTAL

aggr(

if(

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

,

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

,

0

)

,

[JC Description],[Payroll Ending])

)

• Re: Aggr not working correctly with set analysis

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.