Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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?
try like below
if(Date(Today())<Timestamp(Field),'TRUE','FALSE')
or
if(Date(Today())<Timestamp#(Field),'TRUE','FALSE')
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.
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
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'.
May be like this:
If(WorkingDay_2 = 'Y' and MonthStart(Date) = MonthStart(Today()) and Today() < Date, 'TRUE', 'FALSE') as NewField
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.
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
Does anyone have any other ideas?
Checking now