Solved: Re: Help with dates - Qlik Community

hopkinsc · ‎2017-02-15

Hi all,

i have a table that has a timestamp and a flag, the flag is set to 'Y' for the 2nd working day of every month.

what i am trying to achieve is an expression with an IF statement to say..

if(Date(Today())<Date(timestamp),'TRUE','FALSE')

the expression would need to use the dates relevant to the current month and then use the date that is flagged as 'Y'.

I have attached an example, any help would be appreciated.

hopkinsc · ‎2017-02-15

Hi both,

thanks so much for your efforts, i have decided to create anew field in the script containing the single date relating to the current month workingday_2 flag as below..

Let vfrom = date#(monthstart(Today()-16));

Let vend = date#(monthend(Today()-16));

Dates:

LOAD Date,

Year(Date) as Year,

Month(Date) as Month,

WorkingDay_2

FROM

Sample.xls

(biff, embedded labels, table is Sheet1$);

test:

Load Date,

if(WorkingDay_2 = 'Y',Date) as WorkingDay2_Date

Resident Dates

where date(Date)>= '$(vfrom)' and Date(Date)<='$(vend)';

unless you can see why this would cause problems?

View solution in original post

Chanty4u · ‎2017-02-15

try like below

if(Date(Today())<Timestamp(Field),'TRUE','FALSE')

or

if(Date(Today())<Timestamp#(Field),'TRUE','FALSE')

hopkinsc · ‎2017-02-15

Hi, thanks for the reply, but i don't think you're understanding my requirement..

i need to only take into account the dates related to the current month, thats what i don't know how to achieve.

the table of dates and flags have 7 years of data in it, therefore 84 'Y' flags, i only need it to use the 'Y' flag related to the current month, then i need to compare todays date with the date related to that 'Y' flag.

sunny_talwar · ‎2017-02-15

You can use this to estimate the 2nd working day for the current month

LastWorkDate(MonthStart(Today()), 2)

not sure what the comparison needs to be

hopkinsc · ‎2017-02-15

Hi Sunny, thanks for the reply. i need to use the flags in the data model unfortunately, this is a requirement from the business.

so for the current month..

i need an IF statement to look at the dates in the table but only look at the current month dates (01/02/2017-28/02/2017) then compare todays date with the date from the table that is flagged as 'Y' in the WorkingDay_2 field and if today is less than that date then 'True','False'.

sunny_talwar · ‎2017-02-15

May be like this:

If(WorkingDay_2 = 'Y' and MonthStart(Date) = MonthStart(Today()) and Today() < Date, 'TRUE', 'FALSE') as NewField

Report Inappropriate Content · ‎2017-02-15

You can try this one:

=if(Today() <

max({<WorkingDay_2={'Y'},

Date={'>=$(=date#(timestamp(monthstart(Today())))) <=$(=date#timestamp(monthend(Today()))))'}>}Date),

'FALSE', 'TRUE')

This expression comparess today with the second workday of the current month. The set analysis used in Max(Date) only takes the max date where WorkingDay2 = Y and the Date must be between the first and last date of the current month.

hopkinsc · ‎2017-02-15

Hi Sunny,

what you have suggested makes sense but i can not get it to work, see an updated example, could you see if you can get it to work please?

i have changed today to be today()-14 which would make today the 01/02/2017 therefor it is less than the date linked to the 'Y' flag for this month, therefor the result should be TRUE

hopkinsc · ‎2017-02-15

Does anyone have any other ideas?

sunny_talwar · ‎2017-02-15

Checking now