A Set analysis for reducing dataset

[GDK1]

I would be grateful for some help with the following issue.

I have a chart table with the following columns: OrderNumber, OrderDate & ValidityDate with a Filter Panel where the user can select a range for the OrderDate column.

Once the user selects a range I need to display only rows from one ValidityDate.

The ValidityDate should be the Min of ValidityDate that is greater or equal to the Max of the filtered OrderDate  

I created a table with the columns as dimensions, I want to create a measure that will contain an expression for a Set Analysis to reduce the dataset to rows with the required ValidityDate.

For example, given the following data, the user filters the OrderDate between 10/15/2024 & 11/1/2024

RN          ValidityDate     OrderDate    OrderNumber

1              11/6/2024        11/1/2024        165634

2              11/6/2024        11/1/2024        167243

3              11/14/2024      11/1/2024        168475

4              11/13/2024      11/1/2024        168475

5              11/13/2024      11/1/2024        168940

6              11/12/2024      11/1/2024        168475

7              11/11/2024      11/1/2024        168940

8              11/6/2024        10/15/2024      157070

9              11/14/2024      10/15/2024      157070

10           11/13/2024      10/15/2024      157070

11           11/6/2024        9/16/2024        158994

In the chart I want to display the rows with ValidityDate 11/6/2024 And OrderDate between 10/15/2024 & 11/1/2024

RN          ValidityDate     OrderDate    OrderNumber

1              11/6/2024        11/1/2024        165634

2              11/6/2024        11/1/2024        167243

8              11/6/2024        10/15/2024      157070

[DoctorPolidori]

In Qlik Sense, you can use set analysis in a measure to filter data according to your specified criteria.

Here’s how you can structure the expression to filter rows where:

ValidityDate = '11/6/2024'
OrderDate is between '10/15/2024' and '11/1/2024'
Example Expression:

Sum({<
ValidityDate = {'11/6/2024'},
OrderDate = {">=10/15/2024 <=11/1/2024"}
>} MeasureField)

Replace MeasureField with the measure you want to aggregate, e.g. Sum(OrderAmount) or Count(OrderNumber).

{<FieldName = {'Value'}>} is the syntax for set modifiers.

OrderDate = {">=10/15/2024 <=11/1/2024"} filters the range for OrderDate.

If you need to count rows:
You can use Count to count the rows that meet the criteria:

Count({<
ValidityDate = {'11/6/2024'},
OrderDate = {">=10/15/2024 <=11/1/2024"}
>} OrderNumber)

This counts the unique OrderNumber entries that meet the conditions.

[marcus_sommer · Accepted solution]

In addition to the above suggestion - the relevant dates mustn't be hard-coded else the selections could be read, maybe with something like this:

concat({< ValidityDate = {"$(=date(min({< OrderDate = {"$(=date(max(OrderDate)))"}>} ValidityDate)))"}, OrderDate = p(OrderDate) >} OrderNumber, ' + ')

[GDK1]

Thanks, this is exactly what I was looking for