topic Re: Date is less than or equal user selection in QlikView

Date is less than or equal user selection

Zahmed14 — Fri, 15 Nov 2024 23:18:25 GMT

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 ?

Re: Date is less than or equal user selection

rubenmarin — Mon, 18 Jan 2021 12:39:47 GMT

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)

Re: Date is less than or equal user selection

Zahmed14 — Mon, 18 Jan 2021 14:16:18 GMT

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

Re: Date is less than or equal user selection

Zahmed14 — Mon, 18 Jan 2021 14:14:50 GMT

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 ?

Re: Date is less than or equal user selection

rubenmarin — Mon, 18 Jan 2021 14:51:43 GMT

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?