How to Restrict Date Selection in Alternate States Based on a Previous Date?

[Pico]

Hi there,

I want to compare data from two different Alternate States. I've created two distinct filter panes using a date-formatted field: one for the "Snap Date 1" (or Date 1) state, and another for the "Snap Date 2" (or Date 2) state.

The requirement is that Date 1 must always be the more recent date among the two, while Date 2 must be earlier.

What I'm looking for is this: once the user selects Date 1, the available options for Date 2 should only include dates earlier than the selected Date 1.

Note: Date 1 and Date 2 are alternate dimensions from the same field.

How can I achieve this?

I tried Only() function but then the measure is not working with the filter pane.

Thank you

Pico

[Muthukumar_77 · Accepted solution]

I hope below output you are expecting, 

if yes,

Script:

Load Date,Num(Date) as DateNum inline [

Date

****

];

Use this expression in Date 2 filter, 

Date 1 Filter: 

Dimension : Date & Alternate State: Date 1

Date 2 Filter: 

Dimension: =Aggr(Max({<DateNum={"< $(=Max({[Date 1]}DateNum))"}>}Date),Date) 

Alternate State: Date 2

[Pico]

Thank you, it's a cool solution, but I must ask you: how it works??

It's not clear to me what is the aggregate function doing, and why ew got all the data below the selection. Honestly I'm expecting a single date from this formula... I am missing something...

the part "Max({<DateNum={"< $(=Max({[Date 1]}DateNum))"}>}Date),Date)" is clear tho

Thank you

[Muthukumar_77]

Aggr() returns an array of values for the expression calculated over the stated dimension or dimensions.

https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/aggr.htm

Expecting single date means?