4 Replies Latest reply: Apr 14, 2017 9:42 AM by Jacob Salas RSS

    Running total Using Peek.

    Jacob Salas

      Need some help with a weekly running total. for some reason my Formula keeps returning weird Values for My 'HoursRunningTotalWeek'I'd like to have the weekly total add up per employee.  There are many other employees but here is the data for one, Please see below. 





      LABOR_TICKET:
      LOAD * INLINE [

      EMPLOYEE_ID , SHIFT_DATE , Week#, Flag_Weekday, CLOCK_IN , HOURS_WORKED

      101, 42828 , 15 , 1 , 42828.20833 , 0.4

      101, 42828 , 15 , 1 , 42828.22498 , 2.62

      101, 42828 , 15 , 1 , 42828.33359 , 1.84

      101, 42828 , 15 , 1 , 42828.43066 , 6.17

      101, 42829 , 15 , 1 , 42829.19749 , 0.79

      101, 42829 , 15 , 1 , 42829.23049 , 4.34

      101, 42829 , 15 , 1 , 42829.43214 , 6.63

      101, 42830 , 15 , 1 , 42830.20833 , 0.39

      101, 42830 , 15 , 1 , 42830.22479 , 11.11

      101, 42831 , 15 , 1 , 42831.19779 , 1.08

      101, 42831 , 15 , 1 , 42831.24282 , 9.27

      101, 42832 , 15 , 1 , 42832.20833 , 8.03

      101, 42832 , 15 , 1 , 42832.2565 , 1.01

      101, 42833 , 15 , 0 , 42833.19733 , 1.00

      101, 42833 , 15 , 0 , 42833.21525 , 2.20

      ];

       

      Labor_Ticket_Total:

      LOAD*,

      If(Match(Weekday(SHIFT_DATE),'Mon')>0,HOURS_WORKED,

           If(previous(EMPLOYEE_ID)=EMPLOYEE_ID and previous(Week#)=Week# and Flag_Weekday=1 ,

                  HOURS_WORKED+Peek('HoursRunningTotal'))) as HoursRunningTotalWeek

       

      Resident LABOR_TICKET

      ORDER BY EMPLOYEE_ID ,

        SHIFT_DATE ,

        CLOCK_IN ;


      Drop TABLE LABOR_TICKET;





      Ideally I'd like to have the Labor_Ticket_Total table be represented as below:


      EMPLOYEE_ID , SHIFT_DATE , CLOCK_IN , HOURS_WORKED, HoursRunningTotalWeek

      128 , 42828 , 42828.20833 , 0.4, 0.4

      128 , 42828 , 42828.22498 , 2.62, 3.02

      128 , 42828 , 42828.33359 , 1.84, 4.86

      128 , 42828 , 42828.43066 , 6.17, 11.03

      128 , 42829 , 42829.19749 , 0.79, 11.82

      128 , 42829 , 42829.23049 , 4.34, 16.16

      128 , 42829 , 42829.43214 , 6.63, 22.79

      128 , 42830 , 42830.20833 , 0.39, 23.18

      128 , 42830 , 42830.22479 , 11.11, 34.29

      128 , 42831 , 42831.19779 , 1.08, 35.37

      128 , 42831 , 42831.24282 , 9.27, 44.64

      128 , 42832 , 42832.20833 , 8.03, 52.67

      128 , 42832 , 42832.2565 , 1.01, 53.68

      128 , 42833 , 42833.19733 , 1, 0

      128 , 42833 , 42833.21525 , 2.20, 0

      ];



        • Re: Running total Using Peek.
          Stefan Wühl

          You probably want to peek() the created field name?

           

          Peek('HoursRunningTotalWeek')

            • Re: Running total Using Peek.
              Jacob Salas

              If(Match(Weekday(SHIFT_DATE),'Mon')>0,HOURS_WORKED,

                   If(previous(EMPLOYEE_ID)=EMPLOYEE_ID and previous(Week#)=Week# and Flag_Weekday=1 ,

                          HOURS_WORKED+Peek('HoursRunningTotal'))) as HoursRunningTotalWeek



              This is the equation i have tried to use. 

                • Re: Running total Using Peek.
                  Stefan Wühl

                  Right, but I don't see the field 'HoursRunningTotal' you are trying to Peek() in your script. Does it exist?

                  I suggested to use the field 'HoursRunningTotalWeek', using this field should create your running total.

                   

                  Also, use Rangesum() instead of the + operator to avoid issues when one of the operands is NULL.

                   

                  Finally, I would use a different check for changed week or employee to reset the running total.

                   

                   

                  SET ThousandSep=',';

                  SET DecimalSep='.';

                   

                   

                  LABOR_TICKET:

                  LOAD * INLINE [

                  EMPLOYEE_ID , SHIFT_DATE , Week#, Flag_Weekday, CLOCK_IN , HOURS_WORKED

                  101, 42828 , 15 , 1 , 42828.20833 , 0.4

                  101, 42835 , 16 , 1 , 42835.20833 , 0.4

                  102, 42828 , 15 , 1 , 42828.20833 , 0.4

                  101, 42828 , 15 , 1 , 42828.22498 , 2.62

                  101, 42828 , 15 , 1 , 42828.33359 , 1.84

                  101, 42828 , 15 , 1 , 42828.43066 , 6.17

                  101, 42829 , 15 , 1 , 42829.19749 , 0.79

                  101, 42836 , 16 , 1 , 42836.19749 , 0.79

                  101, 42843 , 17 , 1 , 42843.19749 , 0.79

                  101, 42829 , 15 , 1 , 42829.23049 , 4.34

                  103, 42829 , 15 , 1 , 42829.23049 , 4.34

                  101, 42829 , 15 , 1 , 42829.43214 , 6.63

                  101, 42830 , 15 , 1 , 42830.20833 , 0.39

                  101, 42830 , 15 , 1 , 42830.22479 , 11.11

                  101, 42831 , 15 , 1 , 42831.19779 , 1.08

                  103, 42831 , 15 , 1 , 42831.19779 , 1.08

                  101, 42831 , 15 , 1 , 42831.24282 , 9.27

                  101, 42832 , 15 , 1 , 42832.20833 , 8.03

                  101, 42832 , 15 , 1 , 42832.2565 , 1.01

                  101, 42833 , 15 , 0 , 42833.19733 , 1.00

                  101, 42833 , 15 , 0 , 42833.21525 , 2.20

                  ];

                   

                  Labor_Ticket_Total:

                  LOAD*, Weekday(SHIFT_DATE) as Weekday,

                  If(Week# <> Previous(Week#) or EMPLOYEE_ID <> previous(EMPLOYEE_ID),HOURS_WORKED,

                       If( Flag_Weekday=1 ,

                              Rangesum(HOURS_WORKED,Peek('HoursRunningTotalWeek')))) as HoursRunningTotalWeek

                   

                  Resident LABOR_TICKET

                  ORDER BY EMPLOYEE_ID ,

                    SHIFT_DATE ,

                    CLOCK_IN ;

                   

                   

                  Drop TABLE LABOR_TICKET;