Cross Table with Master Calendar

Thu, 29 Jan 2015 00:24:36 GMT

Hi all,

I have data transformed using a cross table. I then transformed the headers into date format. But when I link it to a master calendar, it is not working. Any help would be appreciated: The master table code is taken from qlikview 11 for developers. I changed the highlighted part alone.

[tmpData]:

CrossTable([Sales Period], sales)

LOAD [Location Code],

[201401],

[201402],

[201403],

[201404],

[201405],

[201406],

[201407],

[201408],

[201409],

[201410],

[201411],

[201412]

FROM

[24 months sales.xlsx]

(ooxml, embedded labels, table is Sales);

Final:

Load [Location Code],

Date#([Sales Period], 'YYYYMM') as Period,

sales

Resident tmpData;

Drop Table tmpData;

Temp_Calendar_Range:

LOAD

Min(Period) as MinDate,

Max(Period) as MaxDate

RESIDENT [Final];

LET vMinDate = Peek('MinDate', 0, 'Temp_Calendar_Range');

LET vMaxDate = Peek('MaxDate', 0, 'Temp_Calendar_Range');

DROP TABLE Temp_Calendar_Range; // Cleanup

[Master Calendar]:

Load

AutoNumber(Period, 'PeriodID') as [PeriodID];

LOAD DISTINCT

Year(Temp_Date) * 100 + Month(Temp_Date) as [Period],

Year(Temp_Date) as [Year],

Month(Temp_Date) as [Month],

Date(Temp_Date, 'YYYY-MM') as [Year - Month],

'Q' & Ceil(Month(Temp_Date) / 3) as [Quarter];

LOAD DISTINCT

MonthStart($(vMinDate) + IterNo() - 1) as Temp_Date

AUTOGENERATE (1)

WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

//--- Remove the temporary variables

LET vMinDate = Null();

LET vMaxDate = Null();

Output:

Re: Cross Table with Master Calendar

MarcoWedel — Thu, 29 Jan 2015 00:43:56 GMT

the creation of the period field in your calendar script is different from the Period created in the final table.

Change

Year(Temp_Date) * 100 + Month(Temp_Date) as [Period],

Date(MonthStart(Temp_Date),'YYYYMM') as Period,

hope this helps

regards

Marco

Re: Cross Table with Master Calendar

Thu, 29 Jan 2015 00:55:34 GMT

Thank you so much. I completely overlooked that part of the code.

Re: Cross Table with Master Calendar

MarcoWedel — Thu, 29 Jan 2015 06:23:09 GMT

You're welcome

regards

Marco

topic Re: Cross Table with Master Calendar in QlikView

Cross Table with Master Calendar

Re: Cross Table with Master Calendar

Re: Cross Table with Master Calendar

Re: Cross Table with Master Calendar