topic Problem comparing dates in QlikView

Problem comparing dates

robstar256 — Wed, 29 Jun 2022 20:25:06 GMT

Hey everyone,

I'm trying to do something that seems to be simple, but I can't get the dates to ever be equal.

Here's a simple table:

PROJECTSTATUS:
Load PROJECTID,
PROJECTNUMBER,
STATUS,
STATUSUPDATE,
date(HIDATE, 'M/D/YYYY') as HIDATE,
Time(HIDATE) as HITIME;

I want to create a flag that shows 1 or 0 in a table column depending on if the project status was updated this month or not. I can't seem to get the date to match even with something simple like:

=if( [HIDATE] = '6/27/2022', 1, 0) or =if([HIDATE] = date(6/27/2022), 1, 0)

it's always false:

Appreciate any ideas on how to get these to equate! Thanks!

Re: Problem comparing dates

sidhiq91 — Thu, 30 Jun 2022 02:55:55 GMT

@robstar256 I used below data as sample for my front end evaluation:

NoConcatenate
Temp:
Load * inline [
HIDate
6/27/2022
6/25/2022
6/26/2022
6/23/2022
06/22/2022
];

Exit Script;

Backend Expression in the Straight table:

=if(Date(Date#(HIDate,'MM/DD/YYYY'),'MM/DD/YYYY')=Date('6/27/2022','MM/DD/YYYY'),1,0)

Re: Problem comparing dates

robstar256 — Thu, 30 Jun 2022 15:48:04 GMT

Thanks! that works for the hardcoded date. Now for my real world example, I have a rolling cuttoff date in a variable called vCutOffDate. I was hoping something like this would work, but of course it doesn't match:

SET vCutOffDate = MakeDate( Year(Today()), Month(addmonths(max(HIDATE),-1)),27); // =5/27/2022

=if(Date(Date(HIDATE,'MM/DD/YYYY'),'MM/DD/YYYY') >= Date(Date($(vCutOffDate),'MM/DD/YYYY'),'MM/DD/YYYY'),1,0)

any ideas? Thanks!

Re: Problem comparing dates

sidhiq91 — Fri, 01 Jul 2022 04:16:02 GMT

@robstar256 Any reason why you are hardcoding it to '27', Is your rolling cut off date always 27th of each month? or the last working day?

Re: Problem comparing dates

robstar256 — Mon, 04 Jul 2022 17:07:03 GMT

Hi, we have a cutoff on the 27th for project managers to enter updates. Any idea how to make your statement work with a variable?

thanks!