Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
hopkinsc
Partner - Specialist III
Partner - Specialist III

Help with dates

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.

1 Solution

Accepted Solutions
hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

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

17 Replies
Chanty4u
MVP
MVP

try like below

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

or

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

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

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

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
Partner - Specialist III
Partner - Specialist III
Author

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

May be like this:

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

Not applicable

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
Partner - Specialist III
Partner - Specialist III
Author

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
Partner - Specialist III
Partner - Specialist III
Author

Does anyone have any other ideas?

sunny_talwar

Checking now