Best way to group data into bi-weekly pay periods

PhilD
PhilD Member
in Magic ETL

Several possible possible methods come to mind but each seem as though they could be more complicated than needed. I'm certain this must be a fairly common requirement so I suspect the best way to do this has already been established.

 

My first thought was to use the master calendar and somehow append 'pay period start' and 'pay period end' to the calendar. Although it is not apparent at this moment exactly how to do that, this does seem possible. Then I was thinking that this may be much easier if I didn't need to bring in the master calendar to my datasets... before I waste a lot of time going down the wrong road(s) I thought I would ask the experts out there for some feedback on which direction to go.

 

Looking forward to hearing some thoughts how to approach in the most efficient way. Thanks!

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Having a pay period date dimension is the most flexible option and probably the best one that I'd recommend.

     

    Theoretically you may be able to calculate which pay period it is using a beast mode and the WEEKYEAR() function and dividing by 2. You may need a week offset depending on how / when your actual pay period falls. But like I said this would be theoretical and would need to be tested fully, especially during the new year to make sure everything falls correctly and continues correctly in the new year.

     

    The following beast mode looks at the starting date of the week to determine which calendar week the pay period falls. (For example 2020-2021 -- Jan 1, 2021 falls on a Friday but is being calculated as the last pay period. Depending on how your business operates you may need to tweak the logic a bit.

    ROUND(WEEKOFYEAR(DATE_SUB(`dt`, WEEKDAY(`dt`))) / 2, 0)

    `dt` is your date field.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Having a pay period date dimension is the most flexible option and probably the best one that I'd recommend.

     

    Theoretically you may be able to calculate which pay period it is using a beast mode and the WEEKYEAR() function and dividing by 2. You may need a week offset depending on how / when your actual pay period falls. But like I said this would be theoretical and would need to be tested fully, especially during the new year to make sure everything falls correctly and continues correctly in the new year.

     

    The following beast mode looks at the starting date of the week to determine which calendar week the pay period falls. (For example 2020-2021 -- Jan 1, 2021 falls on a Friday but is being calculated as the last pay period. Depending on how your business operates you may need to tweak the logic a bit.

    ROUND(WEEKOFYEAR(DATE_SUB(`dt`, WEEKDAY(`dt`))) / 2, 0)

    `dt` is your date field.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • PhilD
    PhilD Member

    Thanks!

     

    A brilliant solution... seems to work very well for what I needed. I added the year in front and the only quirk is that the handful of entries having a date prior to the first pay period start of the year are grouped into "period 26" which makes sense. I just used a sort to move this group all the way to the left and provided a drill down in my visualization to allow the user to check the detail items to see the dates making up each category.

     

    // [Year/Period]
CONCAT(
    Year(`Date`),
    '/Period ',
    ROUND(
        WEEKOFYEAR(DATE_SUB(`Date`, WEEKDAY(`Date`))) / 2,
        0
    )
)

      

    Thanks again for your quick response, much appreciated!

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    careful @PhilD 

    test thoroughly the behavior at the start and end of several years. your current implementation will lump days into the wrong year.

     

    YEAR(date) * 100 + weekyear(date) 

     

    THIS IS CORRECT-er BUT UGLY.

    Concat( Year(DATE_SUB(`Date`, WEEKDAY(`Date`))),
    '/Period ',
    ROUND(
        WEEKOFYEAR(DATE_SUB(`Date`, WEEKDAY(`Date`))) / 2,
        0
    )
)

     

    https://www.youtube.com/watch?v=UO9YUfkSh7I&t=73s which is why i always recommend building a date dimension.

    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