Filter For Last Working Day
When a filter based on this custom column is active, the dashboard will reference the last working day.
If the dashboard is checked on Monday or Sunday, Friday's data will be shown.
If the dashboard is checked on Tuesday - Saturday, the prior day will be shown.
If the dashboard is checked on Tuesday - Saturday, the prior day will be shown.
Apply this SQL script to a custom column of a DateDim table and apply a filter based on this new column to the dashboard set to 1.
ifint(
[Date] =
case
when DayOfWeek(CurrentDate()) in (2,3,4,5,6) then adddays(createdate(getyear(now()), getmonth(now()), getday(now())), -1) -- if the day is between Tuesday and Saturday go back return yesterday
when DayOfWeek(CurrentDate()) = 1 then adddays(createdate(getyear(now()), getmonth(now()), getday(now())), -3) -- if the day is a Monday go back 3 days for last friday
when DayOfWeek(CurrentDate()) = 7 then adddays(createdate(getyear(now()), getmonth(now()), getday(now())), -2) -- if the day is a Sunday go back 2 days for last friday
end
, 1, 0)
Updated 03-02-2023
intapiuser
Admin
Joined December 15, 2022