topic Re: Master Calendar in QlikView

Master Calendar

Anonymous — Mon, 25 Jul 2016 02:03:59 GMT

Hi,

I have two tables (Qlik Sense 3.0), with dates, one with historical dates and one with forecast dates. They both start and end at different points in time. My query is... (apologies for my ignorance, this is my first go at this) can the one master calendar look at a min date from the historical data and max date from the forecast data? Or do I need two different calendars or some other solution?

Thanks in advance.

Monica

Re: Master Calendar

Gysbert_Wassenaar — Mon, 25 Jul 2016 08:09:30 GMT

Yes, that's possible. Best to give the date field in both tables the same name so they will both be linked to the master calendar. After loading both tables you can generate a master calendar using the date field that now contains all the historical and forecast dates. Something like this:

Temp:

LOAD

Date(min(FieldValue('MyDate', RecNo())) as MinDate,

Date(max(FieldValue('MyDate', RecNo())) as MaxDate,

AUTOGENERATE FieldValueCount('MyDate') ;

Let vMinDate = peek('MinDate');

Let vMaxDate = peek('MaxDate');

DROP TABLE Temp;

MasterCalendar:

LOAD

MyDate,

Year(Date) as Year,

Month(Date) as Month,

...etc...

LOAD

Date($(vMinDate) + RecNo() -1) as MyDate

AUTOGENERATE $(vMaxDate) - $(vMinDate) + 1 ;

Re: Master Calendar

Mon, 25 Jul 2016 09:27:00 GMT

Just follow procedure like this

OrdersCalTemp:

Load Min(BILLDATE) as MinOrdDate,

Max(BILLDATE) as MaxOrdDate

Resident FACTTABLE;

let EndDate=Date(Peek('MaxOrdDate',0,'OrdersCalTemp'),'$(DateFormat)');

let StartDate=Date(Peek('MinOrdDate',0,'OrdersCalTemp'),'$(DateFormat)');

let caldays=(EndDate-StartDate)+1;

let vToday=num(EndDate);

Cal:

Load RecNo() as RecordNo,

if(RecNo()=1,Date('$(StartDate)'),Date(peek("D")+1)) as D

autogenerate(caldays);

Calendar:

BILLDATE,

........

.......

Resident Cal;

Re: Master Calendar

Anonymous — Tue, 26 Jul 2016 01:19:17 GMT

Hi,

Thank you for your reply. So, do I mention the two tables in the master calendar script? eg Do I need to load two different temp tables to load the two date fields from the two tables?

Re: Master Calendar

Gysbert_Wassenaar — Tue, 26 Jul 2016 07:09:27 GMT

No, like I said first load the two main tables so all date values from those two tables exist in the in-memory database. Rename the date fields if necessary so there's only one date field name in the final data model. Then create the master calendar.