topic Re: How to create a "IsYesterday" field in QlikView Script in QlikView

How to create a "IsYesterday" field in QlikView Script

Sun, 26 Jun 2016 05:39:05 GMT

how to create a "IsYesterday" field in Qlikview Script ...problem is ..if today date is monday then I need IsYesterday to pick Friday and not Sunday.

if today is say 27th June 2016 ( Week end day as Monday)
Date_D	IsYesterday
27-06-2016	No
26-06-2016	No
24-06-2016	Yes
if today is say 23rd June 2016 ( Week end day as Thrusday)
Date_D	IsYesterday
23-06-2016	No
22-06-2016	Yes

Thanks in Advance !!!

Re: How to create a "IsYesterday" field in QlikView Script

marcus_sommer — Sun, 26 Jun 2016 16:03:27 GMT

You could create a (mapping) table like these:

load

Date_D,

if(rowno() = 2, 'Yes', 'No') as IsYesterday,

if(rowno() = 1, 'latest available day respectively current day',

if(rowno() = 2, 'yesterday', 'older days') as dayFlag

Resident YourFactTable order by Date_D desc;

- Marcus

Re: How to create a "IsYesterday" field in QlikView Script

Gysbert_Wassenaar — Sun, 26 Jun 2016 16:04:01 GMT

Perhaps like this: If(num(Date_D) = num(FirstWorkDate(Today(),1)), 'Yes', 'No'). If it doesn't work then your Date_D field probably doesn't contain date values, but text values. In that case first use the Date#() function to turn the text values into date values.

Re: How to create a "IsYesterday" field in QlikView Script

nagaiank — Sun, 26 Jun 2016 16:39:02 GMT

You may add a [Day Number] field in your master calendar which has sequential number for each working day.

Date	Day of the week	Day Number
6/1/2016	Wednesday	98
6/2/2016	Thursday	99
6/3/2016	Friday	100
6/4/2016	Saturday
6/5/2016	Sunday
6/6/2016	Monday	101
6/7/2016	Tuesday	102
6/8/2016	Wednesday	103
6/9/2016	Thursday	104

If [Day Number] is one less, then it is yesterday.

You can take care of not only the weekends but also your holiday calendar in this approach.

Re: How to create a "IsYesterday" field in QlikView Script

varshavig12 — Mon, 27 Jun 2016 05:48:00 GMT

Try this:

Re: How to create a "IsYesterday" field in QlikView Script

varshavig12 — Mon, 27 Jun 2016 05:52:31 GMT

(FirstWorkDate(Today(), 2 ))

Re: How to create a "IsYesterday" field in QlikView Script

Mon, 27 Jun 2016 06:58:32 GMT

Thanks Everyone !!!

I tried nested if ..here is what I used for the results, I used New Field(Is_Yesterday)in my expressions.

Load*,

If(Weekday(Today())= 'Mon',if(floor(num(date(Date_D))) =Today()-3,1,0),if (floor(num(date(Date_D))) =Today()-1, 1 , 0)) as Is_Yesterday

;

Thanks @Marcus Sommer @Varsha Vig @Gysbert Wassenaar @nagaiank

Re: How to create a "IsYesterday" field in QlikView Script

varshavig12 — Mon, 27 Jun 2016 09:12:51 GMT

Instead of nested if try this:

if(Date_D=FirstWorkDate(today(),2),'Yes','No') as IsYesterday