Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
tmumaw
Valued Contributor

Same day previous years

I need a little help in setting some flags in my script.  I set a variable so I know what today's date is, but I need to get the same day previous year.  I have 5 years in my calendar.  Here is my script if anyone can help thanks.

CurrentDate:
LOAD FISPD,
FISYR,
[Billing Date] as Date,
WEDAT,
WKNO
FROM [D:\Qlikview\New Sales\QVD\ZZZWEDAT DaveyTree v1.0.qvd] (qvd)
where [Billing Date] = '$(vToday)';

LET vFisYr = peek('FISYR', 0, 'CurrentDate');
LET vFisPd = peek('FISPD', 0, 'CurrentDate');
LET vWkNo = peek('WKNO', 0, 'CurrentDate');

DROP TABLE CurrentDate;


Temp:
LOAD Distinct
FISPD as DFISPD,
FISYR as DFISYR,
[Billing Date] as Date_ZWKDATE,
WEDAT as DWEDAT,
num(WEDAT, '####0') as WEDAT_Num,
num(Date(Floor([Billing Date]))) as Num_Date_ZWKDATE,
WKNO as DWKNO,
num([Billing Date], '####0') as BillingDateNum,
WeekDay([Billing Date]) as Weekday,
if([Billing Date] <= '$(vToday)' AND FISPD <= '$(vFisPd)' AND WKNO <= '$(vWkNo)', 1) AS YTDFlag,
if([Billing Date] <= '$(vToday)' AND FISPD = '$(vFisPd)', 1) AS MTDFlag,
if([Billing Date] <= '$(vToday)' AND FISPD = '$(vFisPd)' AND WKNO = '$(vWkNo)', 1) AS WTDFlag,
if([Billing Date] <= '$(vPrevToday)' AND [Billing Date] >= '$(vRolling4)', 1) AS RTD4Flag,
if([Billing Date] <= '$(vToday)' AND [Billing Date] >= '$(vRolling3)', 1) AS RTD3Flag,
if([Billing Date] <= '$(vToday)' AND [Billing Date] >= '$(vRolling2)', 1) AS RTD2Flag,
if([Billing Date] <= '$(vToday)' AND [Billing Date] >= '$(vRolling1)', 1) AS RTD1Flag,
autonumber(RowNo(), FISYR) as DayCount
FROM [D:\Qlikview\New Sales\QVD\ZZZWEDAT DaveyTree v1.0.qvd] (qvd)
where FISYR >= '$(vPYear)' and [Billing Date] <= '$(vToday)'
;

Temp1:
Load
DFISYR,
if(DFISYR >= '$(vPYear)' and DFISYR <= '$(vCYear)' and (DayCount = '1'),Date_ZWKDATE) as StartDate
Resident Temp
;

Left Join (Temp1)
Load
DFISYR,
if(DFISYR >= '$(vPYear)' and DFISYR < '$(vCYear)' and (DayCount >= '364'),Date_ZWKDATE,
if(DFISYR = '$(vCYear)' and Date_ZWKDATE = '$(vToday)',Date_ZWKDATE)) as EndDate
Resident Temp
;

Left Join (Temp)
LOAD DFISYR,
Date(Only(StartDate)) as SDate,
Date(Only(EndDate)) as EDate
Resident Temp1
Group By DFISYR;
Drop table Temp1
;
Left Join (Temp)
LOAD * INLINE [DFISPD, DQTR, DMTH
001, Q1, Jan,
002, Q1, Feb,
003, Q1, Mar,
004, Q2, Apr,
005, Q2, May,
006, Q2, Jun,
007, Q3, Jul,
008, Q3, Aug,
009, Q3, Sep,
010, Q4, Oct,
011, Q4, Nov,
012, Q4, Dec
];

Temp2:
Load *,
if(DFISYR >= '$(vPYear)' and DFISYR <= '$(vCYear)' and (DayCount = '1'),1,0) as StartFlag,
if(DFISYR >= '$(vPYear)' and DFISYR < '$(vCYear)' and (DayCount >= '364'),1,
if(DFISYR = '$(vCYear)' and Date_ZWKDATE = '$(vToday)' - 1,1,0)) as EndFlag,
if(DFISYR = '$(vPYear)',5,
if(DFISYR = '$(vPYear)' + 1,4,
if(DFISYR = '$(vPYear)' + 2,3,
if(DFISYR = '$(vPYear)' + 3,2,
if(DFISYR = '$(vPYear)' + 4,1,0))))) as NewHireSFlag
Resident Temp
;
DROP Table Temp
;
FiscalCalendar:
LOAD *,
DFISYR & ' / ' & DQTR as DQYR,
DQTR & ' / ' & DFISYR as DYRQ,
DFISYR & ' / ' & DFISPD as DFYPD,
Right(DFISYR,2) & '-' & DMTH as DYRMTH
Resident Temp2;

Drop Table Temp2;

Labels (1)
1 Reply
Employee
Employee

Re: Same day previous years

Try using the AddYears function
This function returns the date occurring n years after startdate or, if n is negative, the date occurring n years before startdate.

addyears(startdate, n)
so for your case ..
Let vLYday = addyears('$(vToday)', -1);