Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good morning everyone.
I need to select a contract End date selection in Qlik.
I have this, but it is not selecting the dates correct, maybe I need to format the string as well. Not sure how to do this.
=if(date(EndDateFullDate)>date('01-05-2023')and date(EndDateFullDate)<date('31-12-2023'),'yes','no')
It does not work between those dates. Any suggestions
Hi,
You can try Date#() function:
=if(date(EndDateFullDate)>date#('01-05-2023', 'DD-MM-YYYY') and date(EndDateFullDate)<date#('31-12-2023', 'DD-MM-YYYY'),'yes','no')
Convert that int to a date, then compare:
If(Date#(EndDateKey, 'YYYYMMDD') > Now(), 'Expired', 'Active') AS Contract_Status
Also, if EndDateKey is greater than Now(), the contract is actually "Active", not "Expired".
Hi,
You can try Date#() function:
=if(date(EndDateFullDate)>date#('01-05-2023', 'DD-MM-YYYY') and date(EndDateFullDate)<date#('31-12-2023', 'DD-MM-YYYY'),'yes','no')
Thank you so much. It works!!
Hi,
If I want to do this in the editor. I would like to mark the expired contracts. But this is not a date field.
I have a the following:
LOAD
StartdateKey,
EndDateKey,
If(EndDateKey > Now(),'Expired','Active') AS Contract_Status
FROM
[$(QVDLocation)$(vCountry)_QVDs/FactMeteringPointContractLine.QVD](qvd);
Thanks
Theresa
What's the content and format of EndDateKey?
It is an integer.
Convert that int to a date, then compare:
If(Date#(EndDateKey, 'YYYYMMDD') > Now(), 'Expired', 'Active') AS Contract_Status
Also, if EndDateKey is greater than Now(), the contract is actually "Active", not "Expired".
Thank you so much it works