Compare a date field to a variable to generate flag

[MadiF]

Goal: Create a 'is in current month' flag for some data.

Issue: calculation I'm using either returns nothing, returns only one of the two conditions of my if statement, or tells me I'm missing a parentheses, even though I'm not. Interestingly,  I found a qlikview post with the same exact issue from 2013 that was never solved, here: https://community.qlik.com/t5/QlikView-App-Development/Compare-dates-in-Load-Script/td-p/451152#. 

The DateField I want to create the flag for has the format:  M/D/YYYY

The variable I want to compare to has the format: M/D/YYYY (actual value: 9/1/2020)

     How I formed that variable: Let vMaxDate = peek('maxdate') -------- the format of 'maxdate' is also M/D/YYYY

My attempt at creating a flag:  if(DateField>= '$(vmaxdate)', 'Y', 'N') as CurrentMonthFlag  ----- which in the front end just returns 'Y' for everything, I assume because it's not actually reading the variable correctly, similar to what the user described in the post I linked above.

Note: I've tried to alter the formats within the flag in many different ways (wrapping things in 'date' function, 'num' function, using or not using the single quotes $() v '$()', just in case differing formats was the issue) and nothing has worked.

[tlamont]

Is the flag being create in the script?  If so, when you run the script what is the value for the vMaxDate variable? 

[QFabian]

Hi @MadiF ,

you have to convert your dates to numbers from the begining, as you said, using num(YourDate) when you load your data from youir initial source.

Data:

load

fields,

num(Date) as Num

from OriginalSource;

In that way, when you use that field in your expressions for comparison, as they are numbers, you dont need to format anything.

load

GroupingIdField,

num (max (date))  as maxdate

Resident Data

group by 

GroupingIdField;

Check how your dates are from source, because you talk about ' M/D/YYYY', and maybe your defined format is 'M-D-YYYY' :
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD-MM-YYYY';
SET TimestampFormat='DD-MM-YYYY hh:mm:ss[.fff]';
SET FirstWeekDay=0;
SET BrokenWeeks=1;

[MadiF]

Yes I'm trying to create this in the script- the variable returns 9/1/2020 and it's available in the front end for use so the variable itself shouldn't be the problem

[MadiF]

I've tried wrapping everything in num() in various ways and it hasn't worked/solved the issue, unfortunately. Ex:

1)  if(num(DateField)>= '$(vmaxdate)', 'Y', 'N') as CurrentMonthFlag

2)  if(DateField>= num('$(vmaxdate)'), 'Y', 'N') as CurrentMonthFlag

3)  if(num(DateField)>= num('$(vmaxdate)'), 'Y', 'N') as CurrentMonthFlag

4) wrapping the OG variable in 'num' in the set statement

-All didn't work

[A7R3]

This works for me. You may want to give it a try.

InMonthToDate(TempDate, maxdate, 0) * -1 as _CMTD_Flag,
InMonthToDate(TempDate, maxdate, -12) * -1 as _PMTD_Flag,
InYearToDate(TempDate, maxdate, 0, 11) * -1 as _CYTD_Flag,
InYearToDate(TempDate, maxdate, -1, 11) * -1 as _PYTD_Flag,