DP22 - Using Beast Mode to Build Data Storytelling - Adding Business Days

GrantSmith

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