Aggr not working correctly with set analysis

[#1]

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.

[Gysbert_Wassenaar · Accepted solution]

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

    )

[#3]

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

[#4]

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

    )

[#5]

This did not work

[#6]

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

    )

[#7]

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.

[#8]

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