Count the number of working days consumed or remaining month to date

Randyb
Randyb Member
in Magic ETL

I am trying to work out a Beast Mode Calculation on a dataset that I have that will tell me how many working days have elapsed since the 1st of the current month, or how many are left.

I have a value in my dataset which tells me how many working days there are in the current month, I'm struggling with trying to figure out how many have been used up.

I looked at trying to join the DOMO Calendar to the dataset but I can't figure out how I could do that as my data set is produced daily and has a whole bunch of summary numbers and the only date is the last updated date so I'm stuggling with what to join on.

Any help will as allways be appreciated

By the way I"ll be in Salt Lake for Domopalooza tomorrow afternoon so if anyone can help and wants to meet up that would be great.

Drinks on Me !

Best Answer

  • Valiant
    Valiant Coach
    Answer ✓

    It sounds like you're almost there. Here's what I did, maybe that will help bring your across the finish line.

     

    I created a "Working Day" calendar that's just a list of dates (from like 2000-2200) and the "WorkingDayOfMonth" for each date.

     

    I then take my dataset I'm working with and Left Join it to the Working Day calendar for any date field I'm wanting to use. The only thing you need from the Working Day Calendar is the "WorkingDayOfMonth" field, and just rename it based on your joining date. So if you're joining on "Execution Date" you would rename the WorkingDayOfMonth field to ExecutionWorkingDayOfMonth and then repeat if you have multiple dates.

     

    You can also add some constants in your data flow, like Current Day or Last Day of Month, and then join on those dates to get the CurrentWorkingDay and LastWorkingDay to compare to. 

     

    Let me know if you have any questions.


    Sincerely,

    ValiantSpur

     

     

Answers

  • Valiant
    Valiant Coach
    Answer ✓

    It sounds like you're almost there. Here's what I did, maybe that will help bring your across the finish line.

     

    I created a "Working Day" calendar that's just a list of dates (from like 2000-2200) and the "WorkingDayOfMonth" for each date.

     

    I then take my dataset I'm working with and Left Join it to the Working Day calendar for any date field I'm wanting to use. The only thing you need from the Working Day Calendar is the "WorkingDayOfMonth" field, and just rename it based on your joining date. So if you're joining on "Execution Date" you would rename the WorkingDayOfMonth field to ExecutionWorkingDayOfMonth and then repeat if you have multiple dates.

     

    You can also add some constants in your data flow, like Current Day or Last Day of Month, and then join on those dates to get the CurrentWorkingDay and LastWorkingDay to compare to. 

     

    Let me know if you have any questions.


    Sincerely,

    ValiantSpur

     

     

  • Randyb
    Randyb Member

    That worked, I built out a calendar table that had the date, whether or not it was a working day (WHich allowed me to input the Holidays) and then an additional column for days remaining.

    I then joined it to the main dataset using the last update date.

    Now every day I have one date set and it tells me how many working days in the month are left.

    Thanks for your help

    Randy

Categories