Custom Dimension to Count Business Days

Looker Q&A Looker
,
1

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

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:

1 Like
3

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;
1 Like
4

I had the same issue converting from calendar to business days in Looker. I was pulling data from Google Tables, so I did not want to solve the issue in the database but wanted to in the report. The solution I came up with does not take holidays into account, so it may not be the right fix for everyone, but it was sufficient for my reporting requirements.

I created a calculated field with the date_diff formula:

DATE_DIFF(End Date, Start Date)

Then I created a second calculated field that I would actually use in my report that is a very simple solution by dividing by 7 and multiplying by 5 any of the Date_Diff calculations that are above 5:

ROUND(IF(Calendar Days>5,(Calendar Days/7)*5,Calendar Days),0)

I included the rounding formula, since formatting is limited within the column field. This was a simple but not completely accurate solution, but I thought I would share my work around.

2 Likes
5

Here’s a potential workaround if you can’t do it in the database:

Get the week day of the start and end date using:

mod(diff_days(date(2008,01,01), ${your_start_date}) + 1, 7)

This will give you a 0 for mondays and a 6 for sundays.

Then the formula for avoiding western-world weekends of Sat-Sun (not bank holidays) becomes something like:

floor(${calendar_days}/7)*5+mod(${calendar_days},7)+if(${start_date_day_of_week}>${end_date_day_of_week},-2,0)

Basically, for each 7 days consider only 5. Then add the rest and substract 2 days if the start date is before the end date (meaning start on Monday, end on Thursday).

Considering adding special logic if the start or end date can be on a weekend.

6

This is really intriguing.