Hi. We've been asked to track occurrences (absence, late in, early out) for a rolling 30 day period for employees. We're importing an Excel spreadsheet through Box from our 3rd party HR application and have the data required in that dataset but having trouble trying to accomplish this in a beast mode, and not sure if the request is even possible.
Specifics:
- Each absence will count as 1 (one) occurrence
- Any combination of Late in or Early Out that total 2 (two) will count as 1 (one) occurrence when they exceed 5 (five) minutes
- Count/sum these occurrences
- We'd like this on a rolling 30 day calendar (Line + Nested Bar), aggregated company-wide on the top level and then by employee with a breakdown of those occurrences per day for the drill path.
If we did it on a rolling 90 days (3 months) or 180 days (6 months) calendar, would we still have the option to drill down by month to see the most current 30 days?
So far we've been able to create a beast mode that sums the number of occurrences only:
1) calculates if an employee has logged out early and counts as an occurrence
2) calculates the number of times an employee has been absent or late (this is within the same column - ABSENT or YES)
* We haven't taken into account occurrences => 5 minutes at this time
Please let us know feasability of this through a beast mode, any other data needed to create it (working beast mode is below along with sample data), and thanks in advance for any assistance provided!
BEAST MODE:
SUM(CASE WHEN
(CASE WHEN (`Actual End` <> '' )
THEN ((SUBSTRING(`Shift End`, 1, (INSTR(`Shift End`, ':')-1))+ (CASE WHEN (`Shift End` LIKE '%PM%' AND `Shift End` NOT LIKE '12%') THEN 12 ELSE 0 END)) *3600 + SUBSTRING(`Shift End`, INSTR(`Shift End`,':')+1,2)*60)
ELSE '0'
END
- CASE WHEN (`Actual End`) <> ''
THEN ((SUBSTRING(`Actual End`, 1, (INSTR(`Actual End`, ':')-1))+(CASE WHEN (`Actual End` LIKE '%PM%' AND `Actual End` NOT LIKE '12%') THEN 12 ELSE 0 END)) *3600 + SUBSTRING(`Actual End`, INSTR(`Actual End`, ':')+1,2)*60)
ELSE '0'
END
)
> 0
THEN 1
ELSE 0
END
)
+
SUM(CASE WHEN `Late` = 'Absent' OR `Late` = 'Yes' THEN 1 ELSE 0 END)
Data Table from card
