Create master calendar from Fact table in Qlikview

http://www.eenadu.net/homeinner.aspx?category=home&item=break43

We may not have the data for all the dates in Fact Table. We will check the data on date/month/quarter/year time-frame.

We can create master calendar without depend on Fact table or by taking min date and max date.

In general it is best practice to create master calendar based on Fact table date.

For example, We have a simple Fact Table Sales made by a Customer on daily basis.

Let us create a master calendar based on this.

SalesFactData:
LOAD Date(Date#(Date,'DD-MMM-YY')) as Date,EmployeeName,Sales inline [
Date,EmployeeName,Sales
1-Jan-17,E1,1
2-Jan-17,E1,2
3-Jan-17,E1,3
4-Jan-17,E1,1
5-Jan-17,E1,2
1-Jan-17,E2,3
2-Jan-17,E2,1
3-Jan-17,E2,2
4-Jan-17,E2,3
5-Jan-17,E2,1
1-Feb-17,E1,2
1-Feb-17,E1,3
1-Feb-17,E1,1
1-Feb-17,E1,2
1-Feb-17,E1,3
1-Feb-17,E2,1
1-Feb-17,E2,2
1-Feb-17,E2,3
1-Feb-17,E2,2
1-Feb-17,E2,3
1-Jan-18,E1,1
2-Jan-18,E1,2
3-Jan-18,E1,3
4-Jan-18,E1,1
5-Jan-18,E1,2
6-Jan-18,E2,3
7-Jan-18,E2,1
8-Jan-18,E2,2
9-Jan-18,E2,3
10-Jan-18,E2,1
1-Feb-18,E1,2
2-Feb-18,E1,3
3-Feb-18,E1,1
4-Feb-18,E1,2
5-Feb-18,E1,3
6-Feb-18,E2,1
7-Feb-18,E2,2
8-Feb-18,E2,3
9-Feb-18,E2,2
10-Feb-18,E2,3
];

 

SalesFactTableData:
LOAD Country,
Date as _DATE_KEY,
Date as DATE, 
EmpNo, 
EmployeeName, 
Sales FROM SalesFactTableData.qvd(qvd);
// custom function to calculate month difference, can be used in expression too
SET MonthDiff = Num(((year($2) * 12) + month($2)) - (((year($1) * 12) + month($1))));
SET vMaxMonth=13;
// to use in expression: =$(MonthDiff(Today(), Today() + 65))
MinMaxTemp: 
LOAD   min(DATE) as MinDate1, max(DATE) as MaxDate1 Resident SalesFactTableData;
MinMax:
load date(floor(monthstart(max(MaxDate1), -$(vMaxHistoricalMonth)))) as MinDate, max(MaxDate1) as MaxDate resident MinMaxTemp;
drop table MinMaxTemp;
let vGlobalMinDate = num(Peek('MinDate', 0, 'MinMax'));
//let vGlobalMinDate = num(date('1/1/2017')); 
// this is to make sure the first month is Jan 2017
let vGlobalMax = num(Peek('MaxDate', 0, 'MinMax'));
let vToday = Date(Peek('MaxDate', 0, 'MinMax'));
Let vCurrentMonth = text(Date(Peek('MaxDate', 0, 'MinMax'), 'MMM'));
TempCal:
Load date($(vGlobalMinDate) + rowno() - 1) As TempDate AutoGenerate $(vGlobalMax) - $(vGlobalMinDate) + 1;
Drop table MinMax;
MasterCalendar:
Load date(TempDate) as _DATE_KEY,
TempDate as Date,
Day(TempDate) as Day,
Week(TempDate) as Week,Year(TempDate) as Year,Month(TempDate) as Month,Weekday(TempDate) as WeekDay,dual(text(Date(floor(monthstart(TempDate)), 'MMM-YY')), $(MonthDiff($(vGlobalMinDate), TempDate))) as MonthYear, $(MonthDiff($(vGlobalMinDate), TempDate)) as MonthNumber Resident TempCal Order by TempDate ASC;
drop table TempCal;
Advertisements