3 Replies Latest reply: May 16, 2017 3:12 AM by AC BC RSS

    Expression help

    Carl Blunck

      Hi community,

       

      I want to calculate/identify the number of times an employee has performed more than X hours of overtime in a [PayPeriod#].

       

      This be used to help identify individuals who are continually working excessive overtime hours.

       

      X is a variable (vOTLimit) which can be changed through the navigation button extension.  Default value is 5 with variable definition simply written as 5.  No '' wrapping it or = before hand.  If this is wrong let me know also.

       

      Value can be changed to 10,15, 20 or 25.

       

      Overtime hours are calculated through the following set analysis expression which works =

      Sum({$<[Time Category A]={'Worked'},[Payment Type Category]={'Overtime'}>}Hours)

       

      This expression will be sitting in a tablebox chart which will simply have the individuals ID number and their name:

       

        

      PIDPANEmployee NameNumber of pay periods overtime has been worked in excess of (vOTLimit) hours
      XXXXXXBob0
      AAAAAAJane2
      BBBBBBPaul6

       

      I tried using a combo of an if statement and an aggr expression but no luck:

       

      Sum(Aggr(If(Sum({$<[Time Category A]={'Worked'},[Payment Type Category]={'Overtime'}>}Hours)>=(vOTLimit),1,0),distinct([PayPeriod#])))

       

      Made the [PayPeriod#] distinct because the [PayPeriod#] is listed against every individual date overtime is worked.

       

      Sample data:

       

          

      PIDPANTime Category APayment Type CategoryStartTimeDatePayPeriod#sum(Hours)
      XXXXXXWorkedOvertime22/04/2017PP22/20171.5
      AAAAAAWorkedOvertime14/05/2016PP24/20166
      AAAAAAWorkedOvertime28/05/2016PP25/20166
      BBBBBBWorkedOvertime2/01/2016PP14/20164
      BBBBBBWorkedOvertime3/01/2016PP14/20163.5
      BBBBBBWorkedOvertime12/02/2016PP17/20162.5
      BBBBBBWorkedOvertime13/02/2016PP17/20164.5
      BBBBBBWorkedOvertime14/02/2016PP17/20164.5
      BBBBBBWorkedOvertime15/02/2016PP18/20163
      BBBBBBWorkedOvertime16/02/2016PP18/20162.5
      BBBBBBWorkedOvertime17/02/2016PP18/20163
      BBBBBBWorkedOvertime18/02/2016PP18/20163.5
      BBBBBBWorkedOvertime18/03/2016PP20/20162.5
      BBBBBBWorkedOvertime19/03/2016PP20/20163.5
      BBBBBBWorkedOvertime20/03/2016PP20/20164
      BBBBBBWorkedOvertime21/03/2016PP20/20162
      BBBBBBWorkedOvertime22/03/2016PP20/20163.5
      BBBBBBWorkedOvertime23/03/2016PP20/20161.5
      BBBBBBWorkedOvertime24/03/2016PP20/20163.5
      BBBBBBWorkedOvertime29/04/2016PP23/20161
      BBBBBBWorkedOvertime30/04/2016PP23/20164.5
      BBBBBBWorkedOvertime1/05/2016PP23/20163
      BBBBBBWorkedOvertime3/05/2016PP23/20163
      BBBBBBWorkedOvertime4/05/2016PP23/20161.5
      BBBBBBWorkedOvertime5/05/2016PP23/20163.5
      BBBBBBWorkedOvertime1/07/2016PP1/20172.5
      BBBBBBWorkedOvertime2/07/2016PP1/20175.5
      BBBBBBWorkedOvertime3/07/2016PP1/20176
      BBBBBBWorkedOvertime4/07/2016PP2/20171.5