Best way to group data into bi-weekly pay periods
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
-
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!**1
Answers
-
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!**1 -
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!
0 -
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"0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 747 Beast Mode
- 60 App Studio
- 41 Variables
- 687 Automate
- 177 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 397 Distribute
- 114 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 126 Manage
- 123 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive