Is there a way to add a filter to query data between two dates?

Ask the Community
1

I have a data model with records that have two dates: valid_from and valid_to. I would like to create a Holistics report with a date filter that would show all records where the selected date is between valid_from and valid_to. It’s not obvious to me if this is this possible with Holistics?

2

Hi Sterling, if it’s about letting users choose the 2 dates, then this is supported natively using our Date filter.

filter date between 2 dates

Or are you talking about pre-setting the date filters to pull values from these 2 fields in your data model? How do you imagine it to work?

3

I have data that looks like this:

attribute valid_from valid_to
A 2023-01-05 2023-01-31
B 2023-01-22 2023-02-05
C 2023-01-22 2023-06-22

I want to see all records that were valid at a given time specified by a filter. So if I wanted to see all records that were valid on 2023-02-04, I would see records B and C.

4

I see. That’s an interesting one. Basically adding this where condition to the report:

    WHERE valid_from >= ${date_d}
        AND valid_to <= ${$date_d}

I need to check internally, but I think our upcoming Dynamic Models might be the solution here.

We can build a dynamic model like this, and use it to create the final report.

Model attributes_with_filter (date_d: Date) {
  type: 'query'
  
  query: @sql
    SELECT * from {{attributes}}
    WHERE valid_from >= ${date_d}
        AND valid_to <= ${$date_d}
  ;;

  dimension...
}
5

Exactly! I’m really excited about these dynamic models.

6

Hi @SterlingParamoreMSE,
Could you also check this Holistics Guide for a workaround?
You can consider create a single date dimension from your valid_from and valid_to.

7

I’d considered something like that, but was concerned about the table getting too large if we expanded every date range into rows like that. Haven’t tested it out yet to see how bad it might be.