Filter result to get values that older than 45 day

Get Help
1

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:

2

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

3

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 ?

4

@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
5

Thank you very much

6

@flamber

I spoke too quickly sadly :

1
1982×250 15.5 KB

->

2

7

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

8

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

9

@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.

10

@flamber,

Here the table, it will be more understandable :

3
3928×314 17.7 KB

  • 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.

11

@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.

12

Hey flamber

Thanks this was usefull to me.
I have used CountIf(interval([Created At], -15, "day")) to show me users that have been created at for more than 15 days.
On a new column, it show 1 when this is true, and 0 when this is not true.
I am trying to filter to only show the 1 (true values) but for some reason it creates an issue when i filter.
Could you assist?

13

@victoeien Sounds like a different topic. Seems like you should just add a Custom Column like this, if you want to show a 1 for users created within the past 15 days otherwise show a 0
case(interval([Created At], -15, "day"), "1", "0")
If that doesn't help, then open a new topic and describe with more details and screenshots.