Solved: How to filter on last 7 available dates. - Page 2

MikeM1 · 05-10-2022 05:55 AM

I am trying to filter on last 7 available dates in a dataset.

For example I found this post filter-on-the-last-day-with-available-data that allows you to filter on the last day in your dataset but I need the last 7 days in my dataset.

How can I reword this dimension to pull this off?

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

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

MikeM1

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.

Thank you for your time @Dawid. Just so you know the DENSE_RANK() was resulting in the same problems I was seeing with ROW_NUMBER() but also worked fine after removing the PARTITION BY section.

View solution in original post