Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !!!
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
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.
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.
Try this:
(FirstWorkDate(Today(), 2 ))
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
;
Instead of nested if try this:
if(Date_D=FirstWorkDate(today(),2),'Yes','No') as IsYesterday