Filtering on Last Business day of Month

LookerExperts · ‎07-07-2021

Knowledge Drop

Last tested: Feb 2, 2021

dimension: returned_last_business_day {

type: yesno

sql: CASE WHEN EXTRACT( day from DATEADD(day,2,${returned_date_raw}) ) = 1 AND ${returned_day_of_week} = "Friday"

THEN true

WHEN EXTRACT( day from DATEADD(day,3,${returned_date_raw}) ) = 1 AND ${returned_day_of_week} = "Friday" then true

WHEN EXTRACT( day from DATEADD(day,1,${returned_date_raw}) ) = 1 then true else false END;;

Basically, what this does is check to see if the second to last day of the month is Friday, if so that means that the last day of the month for that month was Saturday. If the third to last day of the month is Friday, it means that the last day of that month was a Sunday.

This content is subject to limited support.