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?
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:
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;
This is really intriguing.
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.
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.