Help with dates

[hopkinsc]

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.

[sunny_talwar]

There is only one Y in Feb, but today is not less than the date. Where would you want to see TRUE?

[hopkinsc]

thats correct, as there is only 1 working day no2 in each month.

for todays date everything would be false as todays date is greater than all of the days in feb (that is set at Y).

but if todays date was 01/02/2017 then i would expect to see TRUE on the 01/02/2017.

so if you change the expression for today to be today-14 then that would mimic 01/02/2017.

basically i just want to extract all of the dates relating to the 'Y' flag which would give me 12 dates per year then compare todays date with the date from todays month if that makes sense?

[sunny_talwar]

If I use Today() - 14, I do see a true

Dates:

LOAD Date,

  Year(Date) as Year,

  Month(Date) as Month,

    WorkingDay_2,

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

FROM

Sample.xls

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

Isn't that what you want?

[hopkinsc]

Hi Menno,

thanks for your reply.

this part of your suggestion doesn't work as expected..

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

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

this returns 02/06/2017 - this is the last date with the Y flag set against. it needs to only take into account the current month, so it should only be looking at Feb 2017.

[#6]

Please see New textbox in attached qvw file.

The set analyses only looks at the dates between the beginning and ending of current month.

[hopkinsc]

Hi Sunny,

I am getting a bit confused myself now, i can see that i get a TRUE for the 02/02/2017, but this isnt going to work in my real application. let me try and explain my requirements again, let me know if its still unclear..

This requirement is to set a trigger to make a selection on a 'Period' field.

the correct Period will depend on the date, if the date is less than the workingday_2 date then the previous period would need to be selected. if the date is >= the workingday_2 date then the selection would be the current period.

so what i want is to either..

1.

have a variable that works out what period to select, by comparing the date relating to the Y flag for the current monthYear with today's date.

or

2.

create a new field or variable in the script that is populated with a single date. this would be done by looking at the current month within the Date field where WorkingDay_2='Y'.

i can then use this new field or variable in set analysis or an IF statement.

Does that make sense?

[hopkinsc · Accepted solution]

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?

[sasiparupudi1]

Dates:

Load

Text(Date(Date#(Date,'MM/DD/YYYY h:mm'),'YYYY-MM')) as DatePeriod,

WorkingDay_2,

if(WorkingDay_2='Y' and (Floor(Date#(Date,'MM/DD/YYYY h:mm') )=Floor(Today())),'TRUE','FALSE') as x

FROM

Sample.xls

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

HTH