Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Master Calendar

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
Gysbert_Wassenaar

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 ;


talk is cheap, supply exceeds demand
Not applicable
Author

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;

Anonymous
Not applicable
Author

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?

Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand