Attendance Occurrence Count

Reply
Highlighted
Green Belt

Attendance Occurrence Count

We've been able to accomplish a good portion of the original request within dataflow w/transforms rather than a beast mode, and we'd like to complete this request but need assistance on the last piece of it as the information available online hasn't helped as we'd hoped.

 

We were told that this would fall under Gaps & Islands; looking to group a series of absences into a single occurrence when they happen on consecutive days, and count those multi-day occurrences (as a single occurrence) as well as all other single day absences for a total, per employee? 

EXAMPLE: Bill was absent on Monday, Tuesday, & Wednesday (single occurrence) and then the following week was absent on Monday (single occurrence), then again later in the week on Thursday & Friday (single occurrence).

 

In total, Bill was absent 6 days but we would like to consider this as three (3) occurrences where they're related and on consecutive days; sample of the data we'll be using is below. Any thoughts? Please let us know if any other information would be needed to create this transform. Thanks!

John

 

Screen Shot 2018-12-11 at 11.59.05 AM.png

 

 


Accepted Solutions
Brown Belt

Re: Attendance Occurrence Count

Hi, @John-Peddle,

 

You should be able to do this in a MySQL dataflow. Here's what I would recommend:

  • Get or create a calendar of dates - you can access the Domo default calendar using the Domo Dimensions connector
  • In MySQL dataflow, explode each person name across each date, so if you had 365 days and three people, you would have 3*365 records. Designate if the person was absent on the date. Let's call this the date_person table
  • Join the date_person table to itself, something like this:

 

from date_person as d
left join date_person as d_minus_1
    on d_minus_1.person = d.person
    and dateadd(d_minus_1.date,interval 1 day) = d.date
  • Let's call this the date_compare table
  • Now, you'll need to apply the logic: if the person was absent today but not yesterday, then increment the instance count + 1; if the person was absent today and yesterday, to do NOT increment the instance count. You can apply a similar method as is laid out here: http://knowledge.domo.com?cid=rank 

 

I hope that gets you going in the right direction. 

 

Best, 

Dan


All Replies
Brown Belt

Re: Attendance Occurrence Count

Hi, @John-Peddle,

 

You should be able to do this in a MySQL dataflow. Here's what I would recommend:

  • Get or create a calendar of dates - you can access the Domo default calendar using the Domo Dimensions connector
  • In MySQL dataflow, explode each person name across each date, so if you had 365 days and three people, you would have 3*365 records. Designate if the person was absent on the date. Let's call this the date_person table
  • Join the date_person table to itself, something like this:

 

from date_person as d
left join date_person as d_minus_1
    on d_minus_1.person = d.person
    and dateadd(d_minus_1.date,interval 1 day) = d.date
  • Let's call this the date_compare table
  • Now, you'll need to apply the logic: if the person was absent today but not yesterday, then increment the instance count + 1; if the person was absent today and yesterday, to do NOT increment the instance count. You can apply a similar method as is laid out here: http://knowledge.domo.com?cid=rank 

 

I hope that gets you going in the right direction. 

 

Best, 

Dan

Green Belt

Re: Attendance Occurrence Count

Dan,

Thanks for the response but unfortunately what you've suggested is way beyond my comprehension and skillset; as much as I've tried I'm just not able to follow along and achieve what you're advising can be done with the rank function.

 

Additionally, can you explain why we need to use a separate calendar if both dates and absences already exist in the dataset we're using? Plus, I have no idea what you mean by explode employee names across each date? Sorry!

 

John

Green Belt

Re: Attendance Occurrence Count

@DanB - thanks for the clarification, it really helped to better understand the logic behind your initial post. We're currently working on this and appreciate you taking the time to offer a suggestion and then provide the detailed insight for it. Have a great day!

 

John

Announcements
Dojo Day is Coming! Mark your calendars for May 30th 0900-1700 MST Click here for more information