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

    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.