Best Of

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

This post is the supplement to my presentation “Using Beast Mode to Build Data Storytelling” at my Domopalooza 2022 session. Over the course of that session I talked about several use cases and went over beast modes how to handle those use cases. I also discussed and talked about defining custom period over period definitions. I've outlined links below to posts outlining the different use cases and a breakdown of each. If you find this post or any of the related posts helpful please make sure to give it a like or an awesome below.

Beast Modes:

Formatted Time Duration

Adding Business Days

Links and Images

Sales Pacing Graph

Formatting Text

Custom Period of Period

@Ashleigh I believe this will work for you:

/* check to see if today is Wednesday */
CASE WHEN DAYOFWEEK(CURRENT_DATE()) = 4 THEN
/*its Wednesday, so just show the last 7 days */
	CASE WHEN `date` >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK) AND `date` <= CURRENT_DATE() THEN 'Include'
  ELSE 'Exclude'
  END
WHEN DAYOFWEEK(CURRENT_DATE()) > 4 THEN
/* its Thursday or later, so just show the most recent week */
	CASE WHEN `date` >= DATE_ADD(DATE_SUB(CURRENT_DATE(), INTERVAL 2 WEEK), INTERVAL (4 - DAYOFWEEK(CURRENT_DATE())) DAY)
  AND `date` <= DATE_SUB(CURRENT_DATE(), INTERVAL (4 - DAYOFWEEK(CURRENT_DATE())) DAY) THEN 'Include'
  ELSE 'Exclude'
  END
ELSE
/* its not Wednesday yet, go back an extra week */
	CASE WHEN `date` >= DATE_ADD(DATE_SUB(CURRENT_DATE(), INTERVAL 2 WEEK), INTERVAL (4 - DAYOFWEEK(CURRENT_DATE())) DAY) 
  AND `date` <= DATE_ADD(DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK), INTERVAL (4 - DAYOFWEEK(CURRENT_DATE())) DAY) THEN 'Include'
  ELSE 'Exclude' 
  END
END

couldn't easily test since I was using current_date, but I think should work for you.

@Fadem I have not had trouble saving my drill paths and the columns in my table card staying the way I designed them. Are you clicking on the wrench of the main card and choosing Edit Drill Path and then clicking Edit View and then designing your card (I assume a table card) the way you like and then choose Save and Close? That should be all that you need to do.

The final dataset table view can only be shown or hidden and all the columns in the dataset are shown.

Hope this helps.

@faisalnjit there's a setting when you configure your connector for how long the data is held in cache before refreshing. Different connectors will handle it different ways, but this is not a card setting.