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

Member

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

• Coach

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

• Coach

Not that I am aware of. I would recommend submitting that as an idea in the Happy I could help!

• Coach

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