topic Re: Master calender in QlikView

Master calender

upaliwije — Mon, 03 Feb 2014 16:31:32 GMT

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

Re: Master calender

hic — Mon, 03 Feb 2014 16:37:52 GMT

There are two approaches:

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

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

HIC

Re: Master calender

upaliwije — Mon, 03 Feb 2014 16:42:16 GMT

Hi Henric,

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

Thanks

Re: Master calender

upaliwije — Mon, 03 Feb 2014 16:52:47 GMT

Hi Henric,

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

Thanks

Re: Master calender

upaliwije — Mon, 03 Feb 2014 16:54:10 GMT

Hi Henric

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

Re: Master calender

sureshbaabu — Mon, 03 Feb 2014 17:32:24 GMT

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

Re: Master calender

johanlindell — Mon, 03 Feb 2014 17:51:24 GMT

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