LoV of Dates, Conditional Filter

[moebe]

Hello everybody

I have a customer who extracts data at irregular intervals and stores it in our DWH.
Now I am looking for a possibility to show him a LoV of the available days in the reporting.
My wish would be:

Conditional Filter: latest_etl_rundate
Unless: Select different date from LoV

Getting the latest date is still easy:
select max(etl_valuation_date) from table

But how do I get into a LoV of available days?
If I format this as a date, no suggestion appears.
Unfortunately, if I format this as a string, no suggestion appears either.
And of course I don’t want to lose the functionality of a dimensiongroup (drill etc.).

Do you have solutions for “my” problem?

[moebe]

nobody has an idea or even a solution?

[izzymiller]

This might be embarrassing… but I can’t figure out what LoV stands for, even after some googling! I think I’m probably not alone in that, so maybe expanding that acronym will get people sharing ideas.

[sandsp]

List of Values - as in a list of values in a drop-down selection object. 🙂

[moebe]

Thanks 👍
In looker that are suggestions, sorry @izzymiller for confusion

[moebe]

any ideas?

[izzymiller]

There aren’t currently suggestions for date type fields. I think changing to a string ought to work, but it’s possible that the formatting just isn’t playing nice (since after all, they’re dates). Plus, you mentioned wanting to keep the drilling functionality.

I feel like I’m always full of very hacky ideas, but what if you did something like this:

dimension: etl_valuation_date {
type: date
sql: ${TABLE}.etl_valuation_date ;;
}

dimension: etl_valuation_date_as_string {
hidden: yes
type: string
sql: TO_CHAR SQL business here to get ${TABLE}.etl_valuation_date as a string: ;;
}

filter: available_dates {
type: string
suggest_dimension: etl_valuation_date_as_string
sql: {% condition %} ${etl_valuation_date} {% endcondition %}  (you may have to edit this to go from string --> Date again, using a DATE() function or similar)
}

Creating a hidden string version of the dates, and using that to feed an available date filter that applies it back to the original field. I just mocked it up, so there’s some SQL unknowns and I’m not sure if it will work, but I think it should achieve both suggestions & preserving standard drilling functionality. Let me know if this makes sense or not!

[izzymiller]

Curious if this worked? I feel like it was a pretty bad idea haha, but it would probably be useful to lots of folks to have a way to do limited date suggestions

[moebe]

Hi @izzymiller
sorry for my late response.

Your approach did the job.
I made some changes.

Since looker unfortunately can’t sort on a dimension in ascending or descending order, but only in ascending order, I created a new “hidden” dimension, where I calculate the delta between (sysdate-"lov-suggestion "date) and sort according to it.

Then I build a new dimension (like you) where I put the date as string and sort it by the sort_dimension.

So I have a suggestion sorted descending by date fields.

[izzymiller]

moebe:

Since looker unfortunately can’t sort on a dimension in ascending or descending order, but only in ascending order, I created a new “hidden” dimension, where I calculate the delta between (sysdate-"lov-suggestion "date) and sort according to it.

Yuck, but clever! Glad this worked for you, though it feels like something we could simplify in the product eventually.