topic Master Calendar in QlikView

Master Calendar

Mon, 02 Feb 2015 18:11:11 GMT

I have two different dates that are not related two each other. I thought I could do something like:

CommonDates:

Load

WOID,

RequestDate as CommonDate

Resident WorkOrders;

Concatenate(CommonDates)

Load

BillID,

BillDate as CommonDate

Resident UD_BillAmount;

Set vFM = 7 ; // First month of fiscal year

Set vFD = 6; // First Day of the week (0=Mon, 1=Tue, ... , 6=Sun)

MasterCalendar:

Load distinct

Dual(fYear-1 &'/'& fYear, fYear) as FYear, // Dual fiscal year

Dual(Month, fMonth) as FMonth, // Dual fiscal month

Dual('Q' & Ceil(fMonth/3), Ceil(fMonth/3)) as FQuarter,

Ceil((CommonDate-StartOfFWeekOne+1)/7) as FWeekNo,

Load Year + If(Month>=$(vFM), 1, 0) as fYear, // Numeric fiscal year

Mod(Month-$(vFM), 12)+1 as fMonth, // Numeric fiscal month

Dual('Q' & Ceil(Month/3), Ceil(Month/3)) as Quarter,

WeekStart(CommonDate,0,$(vFD)) as StartOfFWeekOne,

Load

CommonDate,

YearStart(CommonDate,0,$(vFM)) as FYearStart,

Year(CommonDate) as Year,

Month(CommonDate) as Month,

Date(Monthstart(CommonDate), 'MMM-YYYY') as MonthYear,

Week(CommonDate) as ISOWeekNo,

Dual(WeekDay(CommonDate),Mod(WeekDay(CommonDate-$(vFD)),7)+1) as WeekDay,

Day(CommonDate) as Day

// Date(BillDate'MM/DD') as DATEMMDD

Resident CommonDates;

Then I can use CommonDate as my dimension, expression, etc. It is not working.

Thanks,

David

Re: Master Calendar

Peter_Cammaert — Mon, 02 Feb 2015 22:08:15 GMT

Indeed you can do that.

But what are you trying to accomplish? And what is not working? "It" is a bit thin to investigate...

BTW your calendar is not really a Master Calendar in that it contains the same dates as your facts table (no continuous timeline) and could easily be joined to your facts altogether.

Re: Master Calendar

Oleg_Troyansky — Mon, 02 Feb 2015 22:17:57 GMT

Hi David,

Based on your script, you created your MasterCalendar, however your dates in Work Order table and in the Billing table remain detached from the Common Date, and that's most likely the reason why it doesn't work.

The best solution in your case is to create a single fact table that combines both Work Orders and Bills, with a qualifying field that differentiates between the two, and to name both dates with the same field name. Than, your Master Calendar can be linked to that single field and work properly.

You can find multiple references to the process of building a single fact table.

cheers,

Oleg Troyansky

Check out my book QlikView Your Business - available to pre-order on Amazon!