Date range filter based on lastest data

Looker Q&A Looker
1

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

2

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 :light_bulb:

Take care and happy LookML coding :raising_hands:

3

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