Date range filter based on lastest data

[AnalyticsWorld]

Hello guys,

I'm stuck with date filtering- Please suggest

The data is stored in BQ as Month end dates and we have an additional period field to identify the latest data in the database

	Reporting Month end date	Period	Measure
Today's date is 17th July 2024	30-Jun-2024	Current	150,000,000
	31-May-2024	Prior	100,000,000
	30-Apr-2024	Prior	50,000,000
	31-Mar-2024	Prior	150,000,000
	29-Feb-2024	Prior	100,000,000
	31-Jan-24	Prior	50,000,000
	31-Dec-23	Prior	75,000,000
	30-Nov-23	Prior	25,000,000

When you run the report for July 2024 - I want to pick the latest date data (which is 30th Jun 2024) and its last 6 months date (until 31st Dec 2023) using SQL_WHERE or always filters

[francois_care · Accepted solution]

Hello @AnalyticsWorld ,
Thank you for your interest in Looker!

Assuming you have a date dimension "created" and a table `looker-core-demo-ffrancois.thelook_ecommerce.order_items`, below would be a valid LookML approach (among many others I'm sure) using sql_always_where to filter on the 6 months prior to your latest date,

sql_always_where:
    CASE
    WHEN DATE(${created_raw}) = (SELECT MAX(cast(created_at as date)) FROM `looker-core-demo-ffrancois`.`thelook_ecommerce`.`order_items`)
    OR DATE(${created_raw}) BETWEEN
    DATE_SUB((SELECT MAX(CAST(created_at AS DATE)) FROM `looker-core-demo-ffrancois`.`thelook_ecommerce`.`order_items`), INTERVAL 6 MONTH)
    AND
    DATE_SUB((SELECT MAX(CAST(created_at AS DATE)) FROM `looker-core-demo-ffrancois`.`thelook_ecommerce`.`order_items`), INTERVAL 1 DAY)
    THEN TRUE
    ELSE FALSE
    END ;;

To go further, consider using the current date instead of the Max date as this will return a similar result, but may have greater query performances 💡

Take care and happy LookML coding 🙌

[AnalyticsWorld]

@francois_care - Thanks a lot , yeah this should work. I'll try this out