Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
MVP & Luminary
MVP & Luminary

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!

View solution in original post

2 Replies
Highlighted

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.

Highlighted
MVP & Luminary
MVP & Luminary

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!

View solution in original post