Question

# Custom Dimension to Count Business Days

• 2 replies
• 30 views

• 0 replies

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

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

### 2 replies

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:

•

Userlevel 7
+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 bdFROM data;``