Knowledge Base Article

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.
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
No CommentsBe the first to comment