topic Re: Help with dates in QlikView

Help with dates

hopkinsc — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Help with dates

Chanty4u — Wed, 15 Feb 2017 10:49:22 GMT

try like below

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

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

Re: Help with dates

hopkinsc — Wed, 15 Feb 2017 10:55:00 GMT

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.

Re: Help with dates

sunny_talwar — Wed, 15 Feb 2017 10:56:12 GMT

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

Re: Help with dates

hopkinsc — Wed, 15 Feb 2017 11:02:28 GMT

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'.

Re: Help with dates

sunny_talwar — Wed, 15 Feb 2017 11:05:40 GMT

May be like this:

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

Re: Help with dates

Wed, 15 Feb 2017 11:10:55 GMT

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.

Re: Help with dates

hopkinsc — Wed, 15 Feb 2017 11:22:58 GMT

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

Re: Help with dates

hopkinsc — Wed, 15 Feb 2017 11:56:41 GMT

Does anyone have any other ideas?

Re: Help with dates

sunny_talwar — Wed, 15 Feb 2017 11:58:02 GMT

Checking now

Re: Help with dates

sunny_talwar — Wed, 15 Feb 2017 12:01:08 GMT

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

Re: Help with dates

hopkinsc — Wed, 15 Feb 2017 12:09:16 GMT

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?

Re: Help with dates

sunny_talwar — Wed, 15 Feb 2017 12:15:45 GMT

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?

Re: Help with dates

hopkinsc — Wed, 15 Feb 2017 12:20:29 GMT

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.

Re: Help with dates

Wed, 15 Feb 2017 12:25:00 GMT

Please see New textbox in attached qvw file.

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

Re: Help with dates

hopkinsc — Wed, 15 Feb 2017 13:05:24 GMT

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..

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.

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?

Re: Help with dates

hopkinsc — Wed, 15 Feb 2017 13:35:38 GMT

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?

Re: Help with dates

sasiparupudi1 — Wed, 15 Feb 2017 13:42:39 GMT

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