Calendar

Anonymous — Mon, 02 Apr 2012 09:37:03 GMT

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;

Calendar

Anonymous — Fri, 06 Apr 2012 08:57:05 GMT

Hard to say. What is going wrong? Do you get an error while loading?

Does your variables get values?

Btw your script doesn't really make sense to me.

You are trying create a mastercalender bases on the field YearMonth from the Transaction table right?

Is the field YearMonth a full date field or is it only Year and Month?

And can you share your table view (Ctrl + t ) with us? As far as I can see from the script I suspect there is no connection between your Transaction table and your master calender, is there?

Dennis.

topic Calendar in QlikView

Calendar

Calendar