Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

How to create a "IsYesterday" field in QlikView Script

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_DIsYesterday
27-06-2016No
26-06-2016No
24-06-2016Yes
if today is say 23rd June 2016 ( Week end day as Thrusday)
Date_DIsYesterday
23-06-2016No
22-06-2016Yes

Thanks in Advance !!!

7 Replies

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

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

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.


talk is cheap, supply exceeds demand
nagaiank
Valued Contributor III

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

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

  

DateDay of the weekDay Number
6/1/2016Wednesday98
6/2/2016Thursday99
6/3/2016Friday100
6/4/2016Saturday
6/5/2016Sunday
6/6/2016Monday101
6/7/2016Tuesday102
6/8/2016Wednesday103
6/9/2016Thursday104

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
Valued Contributor

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

Try this:

varshavig12
Valued Contributor

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

(FirstWorkDate(Today(), 2 ))

Not applicable

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

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
Valued Contributor

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

Instead of nested if try this:

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

Community Browser