4 4 5 Calendar

[tmumaw]

Hello group, I need your help. We are on a 4-4-5 fiscal calendar, where part of Jan 2010 rolls into Dec 2009. My problem is with my YTD numbers not rolling up correctly. Here is my script if anyone can help great. My YTDFlag is only for Jan 2010 and should be for 2009.

Thanks.

CurrentDate:
LOAD FISPD,
FISYR,
[Billing Date],
WEDAT,
WKNO
FROM (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;

FiscalCalendar:
LOAD FISPD,
FISYR,
[Billing Date],
WEDAT,
WKNO,
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] <= '$(vPrevToday)' AND [Billing Date] >= '$(vRolling4)', 1) AS RTD4Flag,
if([Billing Date] <= '$(vToday)' AND [Billing Date] >= '$(vRolling3)', 1) AS RTD3Flag
FROM (qvd)
where FISYR >= '$(vPrevYear)' and FISYR < '$(vYear)';

[#2]

Tom,

See if this file is of any help to you:

http://community.qlik.com/media/p/71215.aspx

[tmumaw]

Little confused on how I would use the dateisland. I already have a table from SAP which contains my Billing Date, Fiscal Year, Fiscal Period. I think my main problem is setting my YTD and MTD flags based on the calendar.

Thanks

Thom

CurrentDate:
LOAD FISPD,
FISYR,
[Billing Date],
WEDAT,
WKNO
FROM (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;

FiscalCalendar:
LOAD FISPD,
FISYR,
[Billing Date],
WEDAT,
WKNO,
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] <= '$(vPrevToday)' AND [Billing Date] >= '$(vRolling4)', 1) AS RTD4Flag,
if([Billing Date] <= '$(vToday)' AND [Billing Date] >= '$(vRolling3)', 1) AS RTD3Flag
FROM (qvd)
where FISYR >= '$(vPrevYear)' and FISYR < '$(vYear)';