How do I select specific rows in a data set based on a single variable date filter?

D_Markley34

We have products that move through different status. As they move through these status a new row is generated in the data set (see example below) with the product name, the new status, the date the status started and end status date (set to 1/1/2050). The end date of the previous status is also updated. As a user, I want to enter a date and find out what status each product was in. The logic to calculate the correct status would be where selected filter date >= MAX(status started) and filter date < = MIN(end status date). For example using the -date below and filter date of 11/30/2023 would return

Boots - available

Sandals - available

Sneakers - out of stock

Pumps - discontinued

How would this be accomplished using DOMO?

Thank you

Product	Status	Status Start	Status End
Boots	Discontinued	12/4/2023	1/1/2050
Boots	Available	11/1/2023	12/3/2023
Boots	Out of Stock	10/15/2023	10/31/2023
Boots	Available	10/1/2023	10/14/2023
Boots	Pending	7/12/2023	9/30/2023
Sandals	Available	11/22/2023	1/1/2050
Sandals	Out of Stock	9/12/2023	11/21/2023
Sandals	Available	4/1/2023	9/11/2023
Sneakers	Out of Stock	11/12/2023	1/1/2050
Sneakers	Pending	11/1/2023	11/11/2023
Pumps	Available	12/4/2023	1/1/2050
Pumps	Out of Stock	11/1/2023	12/3/2023
Pumps	Available	10/15/2023	10/31/2023
Pumps	Out of Stock	10/1/2023	10/14/2023
Pumps	Available	7/12/2023	9/30/2023

ColemenWilson

Using Variables!

First, create this variable:

Second, create this beastmode:

CASE WHEN Date Variable >= MAX(Status Start) and Date Variable < = MIN(Status End) THEN 'In' ELSE 'Out' END

Third, apply the beastmode from step 2 as a filter on the card and select In = 'In' Or Not In = 'Out'

Fourth, apply different dates in the variable and watch the magic happen!

I tested using your exact data and got the expected results :)

ColemenWilson

Not that I am aware of. I would recommend submitting that as an idea in the https://community-forums.domo.com/main/categories/ideas Happy I could help!

ColemenWilson

Using Variables!

First, create this variable:

Second, create this beastmode:

CASE WHEN Date Variable >= MAX(Status Start) and Date Variable < = MIN(Status End) THEN 'In' ELSE 'Out' END

Third, apply the beastmode from step 2 as a filter on the card and select In = 'In' Or Not In = 'Out'

Fourth, apply different dates in the variable and watch the magic happen!

I tested using your exact data and got the expected results :)

D_Markley34

Thank you that does work I appreciate the help. A follow up question is it possible to set the default value to be the current date?

ColemenWilson

Not that I am aware of. I would recommend submitting that as an idea in the https://community-forums.domo.com/main/categories/ideas Happy I could help!