Date is less than or equal user selection

[Zahmed14]

Hi Guys ,

I have Date field "Date_NEW"  i use it in filter pane , i want to retreive all dates less than or equal filter pane value 

so when user select date  .. the dashboards dimention and measure should run before or equal that date

how to get the user input and pass it in expression for my dimention  and measures ?

[rubenmarin]

Hi @Zahmed14, you can do it with set analysis, like:

Sum({<Date_NEW={"<=$(=Max(Date_New))"}>} Sales)

Dates can be tricky because of the different formats they can have, so maybe you need to do some changes, I usually has a date in number format to apply set analysis, you you can use date as number to apply the filter,and you aso neeed to ignore selection in the field with values in date format:

Sum({<Date_Number={"<=$(=Max(Date_New))"},Date_NEW>} Sales)

[Zahmed14]

Sorry @rubenmarin  am new to qlik  ..  where is the less than or equal user selection in your function 

Date new i put in filter pane like below and should affect the DATE_NEW in table to get all less or equal values  of that date
because i have other expresion which will depend on that dat_New like to have latest date and latest status like in picture  

[Zahmed14]

Thanks for response @rubenmarin 
how to get date values which is less than the user choose 
Here as example this ID have several dates  if i select one date the other status does not appear 

If i choose 1/1/2021 no data for this ID although i want to appear all dates execpt 3/1/2021 
if i choose 15/11/2020 i will get only one record as below  although i want to appear the 14/11/2020 and 27/08 and 30/09 and etc ..

how can i write that in terms of date if possible  ?

[rubenmarin · Accepted solution]

Hi @Zahmed14, maybe that table only has dimensions, you need to add an expression to apply set analysis. If this is true and that table has only dimensions you can change any dimension to be used as a measure, in example, using [All Status]:

Only({<Date_NEW={"<=$(=Max(Date_New))"}>} [All Status]).

the adtes below the selected max Date_New will be set as null (showing as a dash '-'), in add-ons you can uncheck the option to show zero values, this will hide the columns with nulls, leaving only the ones with Date_New equal or lower to the max selected.

If doesn't works.. can you upload a sample to check?