Greater than and smaller than date functions

[TheresaB_4]

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

[Aurelien_Martinez · Accepted solution]

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')

[TheresaB_4]

Thank you so much. It works!!

[TheresaB_4]

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

[steeefan]

What's the content and format of EndDateKey?

[TheresaB_4]

It is an integer.

[steeefan · Accepted solution]

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".

[TheresaB_4]

Thank you so much it works