Master calender

[upaliwije]

I have four date fields in my fact table. I want to connect these dates to a master calender. Please some one help to do it.

Order date

Transaction Date

Goods received Date

Sales Date

Sales Amount

Product

Agent Code

[hic]

There are two approaches:

1. Load four different master calendars.
2. Load a generic master calendar that covers all fields.

Read more about both ways on http://community.qlik.com/blogs/qlikviewdesignblog/2012/08/30/master-table-with-multiple-roles

The generic master calendar is described in one of my comments far down in the thread.

HIC

[upaliwije]

Hi Henric,

I appreciate your prompt reply. I will go through the document come back to you if I need any help

Thanks

[upaliwije]

Hi Henric,

I appreciate your prompt reply. I will go through the document come back to you if I need any help

Thanks

[upaliwije]

Hi Henric

Could you please elaborate a little more of Option  1 possibly with example script

[sureshbaabu · Accepted solution]

Hello,

Please use the script below to create a master calendar for your date column.

please update DATECOLUMN and SOURCETABLE in the script .

You might have to re-do the same for other date columns.

*****************************************

// Please update your DATECOLUMN (3 places) and Source table name (SOURCETABLE)

QuartersMap:  

MAPPING LOAD  

rowno() as Month, 

'Q' & Ceil (rowno()/3) as Quarter 

AUTOGENERATE (12); 

Temp: 

Load 

               min(DATECOLUMN) as minDate, 

               max(DATECOLUMN) as maxDate 

Resident SOURCETABLE

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp; 

TempCalendar: 

LOAD 

               $(varMinDate) + Iterno()-1 As Num, 

               Date($(varMinDate) + IterNo() - 1) as TempDate 

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

MasterCalendar: 

Load 

               TempDate AS DATECOLUMN, 

               week(TempDate) As Week, 

               Year(TempDate) As Year, 

               Month(TempDate) As Month, 

               Day(TempDate) As Day, 

               YeartoDate(TempDate)*-1 as CurYTDFlag, 

               YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 

               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

               WeekDay(TempDate) as WeekDay 

Resident TempCalendar 

Order By TempDate ASC; 

Drop Table TempCalendar;

******************************************************

Hope it helps!!

Thanks

[johanlindell]

Hello,

You could in a case like this consider using a "island" Master calendar and use SetExpression's to calculate the different expressions that youy need. By doing this you can have one calendar selection that will give you a selection for all the "table" fields.

This way you can, when you select January in the Master Calendar, get the Sales Amount for all that was ordered, sold and delivered in January.

This is a very flexible and "nice" solution, however the formulas are a bit more complex. But you can store the "difficult" parts of these formulas in variables.

Here's one example, Set analysis - Date island.

//Johan