Question

Custom Dimension to Count Business Days

  • 24 January 2023
  • 2 replies
  • 30 views

Hi, is it possible to create a custom dimension to only count business days between two dates? The custom dimension I currently have is:

 

diff_days(${test.complete_date},${test.upload_date})

 

Can this be edited to count business days instead of calendar days?


2 replies

Userlevel 7
Badge +1

Actually I found this question intruging if I could come up with a SQL function for it. Only for weekdays and disregarding public holidays.. 

In Snowflake I did this:

Fully unfurled:

IFF(FLOOR((end_d - start_d) / 7) >= 1,
(FLOOR((end_d - start_d) / 7) * 5) +
ABS(LEAST(DATE_PART(DAYOFWEEKISO, end_d), 5) - LEAST(DATE_PART(DAYOFWEEKISO, start_d), 5)) -
IFF(LEAST(DATE_PART(DAYOFWEEKISO, end_d), 5) - LEAST(DATE_PART(DAYOFWEEKISO, start_d), 5) < 0, 1, 0),
LEAST(DATE_PART(DAYOFWEEKISO, end_d), 5) - LEAST(DATE_PART(DAYOFWEEKISO, start_d), 5)
)

And simplified:

WITH data AS (
SELECT
'2023-01-13'::DATE AS start_d,
LEAST(DATE_PART(DAYOFWEEKISO, start_d), 5) AS start_dow,
'2023-02-24'::DATE AS end_d,
LEAST(DATE_PART(DAYOFWEEKISO, end_d), 5) AS end_dow,
end_d - start_d AS diff,
end_dow - start_dow AS dow_diff,
FLOOR(diff / 7) AS weeks_diff,
ABS(end_dow - start_dow) AS dow_diff_abs
)

SELECT
IFF(weeks_diff >= 1, (weeks_diff * 5) + dow_diff_abs - IFF(dow_diff < 0, 1, 0), dow_diff_abs) AS bd
FROM data;

 

Likely something you’d have to put into LookML, especially if you’re also trying to incorporate holidays.  There are other discussions around this, like:

  •  

Reply