Track Attendance Occurrences

John-Peddle
John-Peddle Contributor
in Magic ETL

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 cardData Table from card

 

 

Best Answer

  • John-Peddle
    John-Peddle Contributor
    Answer ✓

    Actually, once we stopped trying to figure out a beast mode for BOTH it seems pretty simple, just not sure if this is correct: count each instance of Late In and Early Out as .5 instead of 1, which would give us a "2-for-1" count for these, along with an absence (counting as 1).

     

    Does that sound like it would be correct, and an easy fix to what we thought would be a more involved request? Reviewing some of the data now, but this may have worked itself out (hopefully!). Thanks

Answers

  • Jarvis
    Jarvis Contributor

    Hey John-Peddle!

    I believe that this is something that can be accomplished via Beast Mode, but it may be more beneficial to configure through a dataflow. In this case, I would recommend getting in contact with your Domo account team for additional assistance. They will be able to specify whether this Beast Mode is feasible for your request, or if a dataflow would be a better option.

     

    Thanks!

     

    -Jarvis

  • John-Peddle
    John-Peddle Contributor

    Rather than worrying about incorporating a threshold for minutes at this time, can anyone assist with how we'd be able to count TWO occurrences as a single instance for Late In's and Early Out's (i.e. 2 times late, 2 times out early, or one instance of each) in a beast mode? 

     

    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

    - Count/sum these occurrences

    - This card will be a rolling 12 month bar graph

     

     

    * additional details relevant to the request can be found in the original submissions, which has since changed. Thanks!

  • John-Peddle
    John-Peddle Contributor
    Answer ✓

    Actually, once we stopped trying to figure out a beast mode for BOTH it seems pretty simple, just not sure if this is correct: count each instance of Late In and Early Out as .5 instead of 1, which would give us a "2-for-1" count for these, along with an absence (counting as 1).

     

    Does that sound like it would be correct, and an easy fix to what we thought would be a more involved request? Reviewing some of the data now, but this may have worked itself out (hopefully!). Thanks

Categories