1 Reply Latest reply on Oct 25, 2018 5:48 AM by Simon Runc

    Running sum split into shifts

    Trevor Hungerford

      Hi all,


      I have a workbook that I am trying to build which has the following;


      • Running sum of the number of connections by each employee ID
      • Rows separated on shift time (6am>2pm, 2pm>10pm,10pm>6am)


      My problem is that some employees start time is staggered to be an hour earlier than the normal shift start, (e.g. employee ID 223 started in the 5am hour, but is working the day shift). As a result, some employee tallies split between two shifts.


      Could anyone assist with setting up the workbook to determine if an employee works the majority of the time in a particular shift, then all the records are allocated to that shift.


      For the example below, employee's 223, 241 and 211 are working the day shift, but started around 5am. I would like their move count show up on the day shift section.



      Thanks in advance.



        • 1. Re: Running sum split into shifts
          Simon Runc

          hi Trevor,


          So I think the following calculation does what you want...it's simpler than it looks, btw


          [Employee Shift]

          {FIXED [Employee Num]:


          {FIXED [Employee Num], [Shift]: SUM([Number of Records])}


          {FIXED [Employee Num]:

          MAX({FIXED [Employee Num], [Shift]: SUM([Number of Records])})}



          So it's using a FIXED LoD (which also means we need to make your date filter in context if you want it to affect the result), which first calculates the number of records for each employee for each shift (in Red)

          Then it finds the MAX of this, and compares it to each employee's Shift total (in Blue)

          Then the final bit uses this to only return the shift which is the max, else NULL and takes the Max of that for each employee (the MAX of something and NULL is the something, in fact it is for MIN too!)


          So this way it returns a single Shift for each employee. Hope that makes sense and is what you were after