Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Master Calendar with Crosstab Data

Hi!  I am fairly new to Qlikview, but am trying to learn.  I cannot figure out how to setup a Master Calendar for multiple crosstab tables. 

My crosstab tables generally all look like the following:

ABSGPActuals:
CrossTable(Month, Actual, 11)
LOAD F1 as "#",
F2 as "Project",
F3 as "CFA",
F4 as "MktSegmt",
F72 as "Sbeg2",
F73 as "PM",
F74 as "Interco",
F76 as "Site",
'Airborne'
as BU,
'2015'
as Year,
'Gross Profit'
as [Program Accounts],

January as "Jan",
February as "Feb",
March as "Mar",
April as "Apr",
May,
June as "Jun",
July as "Jul",
August as "Aug",
September as "Sep",
October as "Oct",
November as "Nov",
December as "Dec"

FROM
[$(vPathExternalData)Finance\2015ABS.xlsx]
(
ooxml, embedded labels, table is [GP $], filters(
Remove(Row, Pos(Top, 10)),
Remove(Row, Pos(Top, 9)),
Remove(Row, Pos(Top, 8)),
Remove(Row, Pos(Top, 7)),
Remove(Row, Pos(Top, 6)),
Remove(Row, Pos(Top, 5)),
Remove(Row, Pos(Top, 4)),
Remove(Row, Pos(Top, 3)),
Remove(Row, Pos(Top, 2)),
Remove(Row, Pos(Top, 1)),
Replace(1, top, StrCnd(null))
))
WHERE
WildMatch (F76, 'Merrimack', 'Ft Worth');

On another tab I have combined all the different crosstab tables by doing the following:

NoConcatenate
  Final:

Load * Resident ABSGPActuals;
Concatenate (Final)

Drop Table ABSGPActuals;

Below is the code I have been trying to make work for my master calendar .... with no luck:

//*************** MinMax Table *************
//Keeps minimum and maximum Date value from Facts table

MinMax:
LOAD
Min(FLOOR(Month)) as MinDate,
Max(FLOOR(Month)) as MaxDate

RESIDENT ABSNBActuals;

LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));
LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));
//LET vToday = $(vMaxDate);
LET vToday = NUM(TODAY());

//*************** Temporary Calendar *************
//Generates a single table with one field containing all existing dates between MinDate and MaxDate.

TempCal:
LOAD
date($(vMinDate) + rowno() - 1) AS TempDate
AUTOGENERATE
$(vMaxDate) - $(vMinDate) + 1;

DROP TABLE MinMax;

//*************** Master Calendar ***************
//Disconnected during the Date Island exercise by renaming TempDate as IslandDate


MasterCalendar:
LOAD
TempDate AS Month,
// Week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month1,
// Day(TempDate) AS Day,
// Weekday(TempDate) AS WeekDay,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,
Date(monthstart(TempDate), 'MMM-YY') AS MonthYY//,
// Week(TempDate)&'-'&Year(TempDate) AS WeekYear,
// inyeartodate(TempDate, $(vToday), 0) * -1 AS CurYTDFlag,
// inyeartodate(TempDate, $(vToday), -1) * -1 AS LastYTDFlag
RESIDENT TempCal
ORDER BY TempDate ASC;

DROP TABLE TempCal;
// 

I am hoping someone can help!  Thank you in advance!!!

12 Replies
Highlighted

Do not reply to the thread in your Inbox, if you want to upload files. Instead, in your Inbox click the blue discussion title. Your discussion will be opened in full view, and you'll get an expanded set of functions and features when replying to a post. Including the "Use advanced editor" link Sunny mentioned.

Best,

Peter

Highlighted
Not applicable

Thank you Sunny!  That worked!!!!  I really appreciate your help!!!!

Highlighted

I am glad I was able to help you out Kelly