7 Replies Latest reply: Jan 29, 2018 2:03 AM by arvind pednekar Branched to a new discussion. RSS

    Associating Master Calendar with Fact tables

    Mohamed Ilyas Pitchai Mohamed



      I have below fact tables:

      1. PurchaseOrders (Date Field: DATE(PURCHS_ORDR_DATE) AS PO_DATE)

      2. Contract_Lines (Date Field: DATE(CONTRACT_DATE) AS CONTRACT_DATE)


      Below is my calendar:


      Load min(PO_DATE) as minDate, max(PO_DATE) as maxDate

      Resident PurchaseOrders;


      Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

      Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

      DROP Table Temp;




      $(varMinDate) + IterNo()-1 As Num,

      Date($(varMinDate) + IterNo() - 1) as TempDate

      AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);




      TempDate As PO_DATE,

      week(TempDate) As Week,

      year(TempDate) As Year,

      Month(TempDate) As Month,

      Day(TempDate) As Day,

      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;



      I'm showing year filter in filter pane and listing the POs and contracts in table with the year, PO/contract number and total line items.

      The issue is, when i select a year from the filter, its filtering only Purchase Orders and not contracts.


      What you suggest i should be doing as a standard practice when it comes to year/date filters like this scenario where there are multiple tables involved.


      Your help is much appreciated.

      Regards, Ilyas