DP22 - Using Beast Mode to Build Data Storytelling - Adding Business Days
This post is part of a series of beast modes I presented on during my “Using Beast Mode to Build Data Storytelling” Domopalooza 2022 session. If you find this post helpful please make sure to give it a like or an awesome below.
Use Case
When evaluating different carriers for performance we needed a simplistic calculation to quickly determine how often they were delivering packages on time. The delivery days promised by the carriers are in business days however the days an order was shipped and when it was delivered are actual calendar days. This beast mode allows us to add a specific number of business days to a date to compare to when a delivery was made. This calculation assumes a work week of Monday through Friday and doesn’t consider the days in which the business was closed (for example holidays).
Beast Mode
`Ship Time` + INTERVAL ( FLOOR(`Estimated Delivery Days`/5) * 7 + CASE WHEN MOD(`Estimated Delivery Days`,5) + DAYOFWEEK(`Ship Time`) >= 7 THEN 2 - FLOOR(DAYOFWEEK(`Ship Time`)/7) ELSE 0 END + MOD(`Estimated Delivery Days`,5) ) DAY
Beast Mode Breakdown
FLOOR(`Estimated Delivery Days`/5) * 7
`Estimated Delivery Days`/5
converts the business days into calendar weeks as there are 5 business days per week (Monday - Friday).
FLOOR
returns the whole number rounded down to get the number of full weeks. Partial weeks will be added later.
Multiply by 7 to convert to calendar days
DAYOFWEEK(`Ship Time`)
DAYOFWEEK takes a date and returns a value for the number of the day of the week (1 – Sunday … 7 – Saturday)
MOD(`Estimated Delivery Days`,5)
This is the number of business days left in the partial business week
CASE WHEN MOD(`Estimated Delivery Days`,5) + DAYOFWEEK(`Ship Time`) >= 7 THEN
Putting these both into In simpler terms - Does the partial week cause us to go into the weekend (beyond Saturday / day 7)?
FLOOR(DAYOFWEEK(`Ship Time`)/7)
Returns 0 for Sunday – Friday, 1 for Saturday. DAYOFWEEK
will return 1-6 Sunday-Friday and 7 for Saturday. Dividing this by 7 returns a number < 1 for Sunday-Friday and 1 for Saturday. FLOOR
converts any decimal less than 1 to be 0 by dropping the decimals. This is an alternative way to do
CASE WHEN DAYOFWEEK(`Ship Time`) = 7 THEN 1 ELSE 0 END
2 - FLOOR(DAYOFWEEK(`Ship Time`)/7)
If the start date was Saturday only add one day to get throught the weekend instead of the standard 2 days
MOD(`Estimated Delivery Days`,5)
Use the MOD
function to divide the estimated business days we’re adding and get the remaining to add to the calendar date.
Final Result
Bonus - On Time Deliveries
We can take this one step further and use another CASE
statement to bucket our deliveries into either On-Time or Late
CASE WHEN `Delivery Time` <= (`Ship Time` + INTERVAL ( FLOOR(`Estimated Delivery Days`/5) * 7 + CASE WHEN MOD(`Estimated Delivery Days`,5) + DAYOFWEEK(`Ship Time`) >= 7 THEN 2 - FLOOR(DAYOFWEEK(`Ship Time`)/7) ELSE 0 END + MOD(`Estimated Delivery Days`,5) ) DAY) THEN 'On-Time' ELSE 'Late' END
**Did this solve your problem? Accept it as a solution!**
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
- 748 Beast Mode
- 60 App Studio
- 41 Variables
- 688 Automate
- 177 Apps
- 453 APIs & Domo Developer
- 48 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 397 Distribute
- 114 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 127 Manage
- 124 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