How to create a "IsYesterday" field in QlikView Script

[#1]

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

[marcus_sommer]

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

[Gysbert_Wassenaar]

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.

[nagaiank]

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.

[varshavig12]

Try this:

[varshavig12]

(FirstWorkDate(Today(), 2 ))

[#7]

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

[varshavig12]

Instead of nested if try this:

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