2 Replies Latest reply: Feb 2, 2015 5:17 PM by Oleg Troyansky RSS

    Master Calendar

    David Young

      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

          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

            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!