I have a solution for this, but it does not consider Holidays, as that is almost impossible to get right, especially across timezones.
However, here is what you can do to exclude weekends:
dimension: datediff_no_weekend {
type: number
sql: datediff(second, ${task.started_time}, ${task.finished_time})
- (datediff(wk, ${task.started_time}, ${task.finished_time}) * 172800.0);;
}
How it works:
We calculate the difference between the started_time and the finished_time. I suggest using seconds as it gives you the most accurate result. We then subtract the number of weeks that are between the two dates multiplied by the total number of seconds in a weekend (172800).
For example, let's take Thursday, 25th January to Tuesday 30th of January. The difference in seconds is 432000. We then count how many weeks difference are between the two dates, the answer is 1. So we can calculate:
432000 - (1 * 172800) = 259200
The total working time between Thursday, 25th of January and Tuesday 30th of January is 259200 seconds, or 3 days.
Hope this helps,
blue
LinkedIn
Twitter
