Having trouble getting my calendar to load, any ideas why?
CALENDAR TABLE
//-------------------------------------------------------------
//Get Dates from Data
//-------------------------------------------------------------
DateTemp:
LOAD (Date(Date#(YearMonth,'YYYY-MM-DD'),'DD/MM/YYYY')) as YearMonth
RESIDENT Transactions
ORDER BY YearMonth Asc;
LET vMinDate = Num(Peek('YearMonth', 0, 'DateTemp')); //LET ... evaluates the expression on the right side of the ’=’ before it is assigned to the variable
LET vMaxDate = Num(Peek('YearMonth', -1, 'DateTemp'));
LET vToday = Num(today());
//-------------------------------------------------------------
//Temporary Calendar
//-------------------------------------------------------------
TempCalendar:
LOAD
$(vMinDate)+IterNo()-1 AS Num,
Date($(vMinDate)+IterNo()-1) AS TempDate
AUTOGENERATE 1 WHILE ($(vMinDate)+IterNo()-1) <= $(vMaxDate);
//-------------------------------------------------------------
//Master Calendar
//-------------------------------------------------------------
MasterCalendar:
LEFT KEEP (Transactions)
LOAD TempDate AS %_DateKey,
TempDate AS %_DateKeyCA,
week(TempDate) AS Week,
year(TempDate) AS Year,
month(TempDate) AS Month,
day(TempDate) AS Day,
weekday(TempDate) AS WeekDay,
'Q' & ceil(month(TempDate)/3) as Quarter,
date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,
week(TempDate)&'-'&Year(TempDate) AS WeekYear,
Year2Date(TempDate, 0, 1, $(vToday))*-1 AS CurYTDFlag,
Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LastYTDFlag,
num(month(TempDate)) as Period
RESIDENT TempCalendar
ORDER BY TempDate Asc;
Drop table TempCalendar;
Drop table DateTemp;
TRANSACTIONS TABLE
Transactions:
LOAD YearMonth,
RamsNo,
ScriptNo,
Nappi9,
Nappi6,
PayOffice,
MedCode,
Pharmacist,
Barcode,
ShopArea,
ProfFee,
CostValue as Sales,
GrossVal,
SepCostValue,
Quantity
FROM
\\10.240.12.41\Qlikview\QVData\Live\QVD\FrontAndDispTransMaster.qvd
(qvd)
Where GroupCode=14;
