Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Master Calendar

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Master Calendar

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!

2 Replies

Re: Master Calendar

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.

MVP
MVP

Re: Master Calendar

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!

Community Browser