Available work day

ewelsh
ewelsh Member
in Archive

Hi!

 

Is there a way to get available workdays onto a dataset? For us, that is basically M-F every week of the year regardless of holidays. The current field in here now called 'Date Worked' assumed the person entered a timesheet for that day. we want to capture the 'date worked' regardless of whether they did a timesheet for that day or not. Thanks

Tagged:

Comments

  • zcameron
    zcameron Domo Employee

    If you contact Domo Support, they can add a dataset to your instance that has all the dates from 2010 to 2030 and includes a flag for whether a date is a weekday or not. You could use that as a base table (after filtering it down to the range you care about, and only dates that are flagged as a weekday) and LEFT JOIN your timesheet data onto it. Then you'll have a dataset with all weekdays on it and data for the days that have the timestamp.

     

    You'd want to call/email/buzz Domo Support and request Domo Dimensions calendar dataset. They can get it set up for you.

  • kshah008
    kshah008 Contributor

    @ewelsh, tagging you to check out zcameron's reply. 

  • user050653
    user050653 Member

    Did anyone confirm this worked? 

     

    I want to be careful with the join specifically because it is the major data flow that is used to power a majority of cards currently. 

     

    Should it be a left join on the calendar?

     

    Chris

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    Chris,

    You should always do testing before pushing a dataset into production.  Don't just make changes on a production data flow... Make a copy of the ETL and try it.  But yes, it should be a LEFT JOIN assuming your Date table is on the LEFT.  

     

    A left join will keep all the rows from the left side and any matches from the table on the right.  If there is no match on the right, the values in that column will be NULL.

     

    Think through your use case though.  If in your timesheet data there is one row per employee per day they submitted a timesheet, and you want to be able to ask:

    "of the 40 working days over the last two months what percent of the available days did Employee A work," you can't get that out of just a LEFT JOIN to a date table, because if the employee didn't submit a timesheet for Jan 5th, you'll just have a row for Jan 5 with the date, but not the employee num (because that comes from the RIGHT side of the JOIN).

     

    If your granularity is one row per day that encompasses all employees, then you'll be fine.

     

    In summary, make sure you've thought through your requirements and tested it before moving something you don't understand into a production workflow.  If your requirement is the first use case, here's a youtube video solving the problem!

    https://www.youtube.com/watch?v=Xb4QgKYgaqg

     

     

     

     

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories