Date filters allow range and null selection

Paul_Narup
1 yr ago
3replies
Answered

Use case: Users need to be able to select a date range (YTD 2023 for example) and also have the ability to return the rows that have a NULL date. Using the multi select dropdown is not an suitable option. What is the best course of action?

- Senior Director of Product
- Ian_Macdonald
- 1 yr ago
- Reported - view
Hi Paul,

Can I clarify a couple of things with you?

Are you looking to retrieve rows for, say, a sales transaction, where the date column has not been recorded and is null, or,

Want a transactions result set for all dates in a contiguous fashion, even if there are no transactions for some dates?

Thanks,

Ian
- Paul_Narup
  
  1 yr ago
  
  Reported - view
  Ian Macdonald It would be closer to #2. Select the rows with transaction dates in 2023 but also include those with no transaction date. For example, selecting every 2023 date in a dropdown and also selecting null ('--') produces the correct result. But this removes the ability to use the built in date calendar.
- Senior Director of Product
- Ian_Macdonald
- 1 yr ago
- Official response
- Reported - view
Hi Paul,

Create a parameter that contains all your "proper dates":

Create a List that includes the null dates ("--") Member and the Parameter:

Select the List:

Se the Parameter to be of type Calendar:

Select your range of dates:

And the null dates Member will be included:

Of course, this will pick up all data where the date is null, not just in the range I've selected, because, of course, the date is null so you can't tell what range it falls into.

Hope that helps.

Ian