cancel
Showing results for
Did you mean:
Helper II

## Running total per employee per week where total resets at end of each week

Hello,

I want to calculate a running total column for employees during a single work week and flag each number day after they hit 40 hours (preferabbly 2 different measures)

Running total is per workday until we hit a new weekend date and flag at 40 is blank unless the running total is >40.

IE 4/7/21 for employeeid A when we hit 42 total hours worked, it is flagged. These below results are ideal. Thanks!

 Workdate EmployeeID Weekend Hours Worked Running Total Flag at 40 4/4/2021 A 4/10/2021 10 10 4/5/2021 B 4/10/2021 10 20 4/6/2021 A 4/10/2021 10 30 4/7/2021 B 4/10/2021 15 45 x 4/8/2021 A 4/10/2021 15 57 x 4/9/2021 B 4/10/2021 10 65 x 4/4/2021 B 4/10/2021 10 10 4/5/2021 A 4/10/2021 10 20 4/6/2021 B 4/10/2021 10 30 4/7/2021 A 4/10/2021 12 42 x 4/8/2021 B 4/10/2021 10 55 x 4/9/2021 A 4/10/2021 10 67 x 4/10/2021 B 4/10/2021 10 75 x 4/11/2021 A 4/17/2021 10 10 4/12/2021 A 4/17/2021 10 20 4/13/2021 B 4/17/2021 10 10 4/14/2021 A 4/17/2021 12 30 4/15/2021 B 4/17/2021 10 20 4/16/2021 A 4/17/2021 10 42 x 4/17/2021 B 4/17/2021 10 30
1 ACCEPTED SOLUTION
Super User IV

@sparkplug93751 , a new column like

new column =
var _sum = sumx(filter(Table, [Weekend] = earlier([Weekend]) && [Workdate] <= earlier([Workdate]) && [EmployeeID] = earlier([EmployeeID])),[Hours] )
return
if(_sum >40, "X", blank())

or

new column =
var _sum = sumx(filter(Table, [Weekend] = earlier([Weekend]) && [Workdate] <= earlier([Workdate]) ),[Hours] )
return
if(_sum >40, "X", blank())

Proud to be a Super User!

Super User IV

@sparkplug93751 , a new column like

new column =
var _sum = sumx(filter(Table, [Weekend] = earlier([Weekend]) && [Workdate] <= earlier([Workdate]) && [EmployeeID] = earlier([EmployeeID])),[Hours] )
return
if(_sum >40, "X", blank())

or

new column =
var _sum = sumx(filter(Table, [Weekend] = earlier([Weekend]) && [Workdate] <= earlier([Workdate]) ),[Hours] )
return
if(_sum >40, "X", blank())

Proud to be a Super User!

Announcements