Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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 ;
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;
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?
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.