Filter result to get values that older than 45 day

Hello,

I need to filter to get all results that the last validated date of the result is older than 45 days. I think something like this could be ok, but I don't know how to format it. There is variables or something that I could use ? :

between([Last Validated Date], "today - 45 days", "today - 45 days and older")

Any idea about it ? :grimacing:

Hi @Stekino
Have a look at interval(): https://www.metabase.com/docs/latest/users-guide/expressions.html

Hi @flamber

I have try this : CountIf(interval([Last Validated Date], -45, "day"))
-> It display all result that Last validated date are during the 45 pastest day

I would like to get all results that "Last Validated Date" are older than 45 days, have you an idea how to get this ?

@Stekino Metabase does not have a "relative before" functionality yet:
https://github.com/metabase/metabase/issues/4083 - upvote by clicking :+1: on the first post
But you should be able to work around it by subtracting with something like this:
Count - CountIf(interval([Last Validated Date], -45, "day"))
For reference: How to compare three weeks of booking data?

1 Like

Thank you very much

@flamber

I spoke too quickly sadly :

->

2

@Stekino
I'm not sure I understand. What is the problem?

@flamber,

Sorry I was probably not clear :

I need to get all results that "Last Validated Date" are older than 45 days -> The value "June 30, 2021, 12:20 PM" (screenshot) has only 28 days old and is not older than 45 days :grimacing:

@Stekino I still don't understand. Then you're looking for filters - not aggregations like CountIf.
You can create a Custom Column with a case:
case(interval([Last Validated Date], -45, "day"), "within45", "olderthan45")
Without understanding your data, then it's difficult. Try making an example with Sample Dataset.

@flamber,

Here the table, it will be more understandable :

  • I need to count results without condition
  • I need to count and visualize with the condition "Last Validated Date are older than 45 days"

If it's still not understandable, I don't want to loose your time anymore, I appreciate a lot your help.

@Stekino I would need to raw data - not the aggregated data. I cannot see what's behind the data.
I would recommend that you do it in SQL instead.