Table calculation - Calculating between two dates - but excluding weekends.

Table calculation - Calculating between two dates - but excluding weekends.
Hi there, could anybody help with a table calculation for it to exclude weekends, so calculating how long a process step took only in working days.

Any advise would be really appreciated.

Many thanks,
erika

0 2 2,432
2 REPLIES 2

I think this might be really difficult to do in a table calculation, depending on the structure of your data.

If you are trying to do this calculation “down the table” ie: your data looks like

date| field
09-01 | lala
09-02 | lalala
09-03 | lalalal

You can probably do this by adding a day_of_week or day_of_week_number field into your explore (these should be already part of the dimension group / can be easily added by your developer) and then in your table calculation, use an if() statement to check if the day_of_week_number = 6 or 7 / day_of_week = “Saturday” or “Sunday”.

If you’re trying to do it “across the table” and your data looks like this, instead:

date1 | date2
09-01 | 09-05
09-20 | 10-30

and you want to get the time between date1 and date2 for each row, I think you may be out of luck using table calculations since the actual data just is not there. You could approximate it by doing a diff_days and dividing by 7 to figure out weeks, and then multiplying that by 2 to get the approximate # of weekends, but it would be imprecise.

You can do this in SQL fairly effortlessly, though. If you have access to the model, check this out: https://help.looker.com/hc/en-us/articles/360023861113-How-to-Count-Only-Weekdays-Between-Two-Dates
If you don’t have access, send that to your LookML developer 🙂

Thank you so much for getting back to me.
Yes, im afraid im looking for the “across the table” calculations. Don’t have access the SQL bit, so will need to contact the LookML developer.

Many thanks,
erika

Top Labels in this Space
Top Solution Authors