Filter looker tile to show last 7 reported days in dataset?

Question

How do I filter down to the last 7 "REPORTED" days in a dataset.

Simply filtering the last 7 days will not work as the reported days are not always consecutive. I cannot use something like last 10 days to catch them because I need to limit the set to 7.

Say I have a date column in my dataset that looks like this:

COMPLETED_DATE:

05/09/2022
05/06/2022
05/05/2022
05/04/2022
05/03/2022
04/20/2022
04/18/2022
04/17/2022
04/16/2022

I need looker to display the results related to the last 7 reported dates like this:

COMPLETED_DATE:

05/09/2022
05/06/2022
05/05/2022
05/04/2022
05/03/2022
04/20/2022
04/18/2022

Is this something I can do with the built in filters or will I need to write some LookML to get this done?

I am guessing I might be able to write a dimension to do this. I found online a dimension for filtering on last date. I am not sure how I can reword this to filter on last 7 days.

dimension: latest_date { type: yesno sql: ${created_date} = max(${created_date}) ;; }

Source

Answer 1

The problem was resolved by editing the query data to include a RANKED row. This lets us filter on the rank based on this column.

This was worked out over a few replies on the looker forums here:

how-to-filter-on-last-7-available-dates

MY solution was as follows:

Turns out removing the “partition by portion” fixed the count issue. I removed it after seeing an error that said the ORDER_BY was required with the OVER clause and for some reason that prompted me to check what it would say if I removed the PARTITION BY portion. For whatever reason the counts are showing up now using ROW_NUMBER and RANK without issue.

Select received_date, ROW_NUMBER() OVER(ORDER BY received_date DESC) from
(SELECT distinct CAST(received_time as DATE) received_date FROM TABLE_NAME) s
WHERE received_date <= '2022-04-12'
ORDER BY received_date  DESC

AND

Select received_date, RANK() OVER(ORDER BY received_date DESC) from
(SELECT distinct CAST(received_time as DATE) received_date FROM TABLE_NAME) s
WHERE received_date <= '2022-04-12'
ORDER BY received_date  DESC

Both results in:

received_date   RANK
2022-04-12      1
2022-04-11      2
2022-04-10      3
2022-04-08      4
2022-04-07      5
2022-04-06      6
2022-04-05      7

As you can see I get the count I was looking for as we do not have a 04-09 date in the available dates and the count moves on to the next date.