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;
