1 Reply Latest reply: Jan 26, 2016 2:10 PM by Sangram Reddy RSS

    Single fact table with 4 date fields - Single Calendar (QSense)

    Deli Hassan

      Hi,

       

      I have a single fact table that contains 4 different date fields. I want to be able to report on all date fields.

       

      The original structure of this fact table was as follows :

       

      Table A:

      ---------------------------------

      incidentDate

      createdOnDate

      fieldA

      fieldB

      'abc' & RowNo() as DateLink

       

       

      Table B:

      ---------------------------------

      incidentDate

      callTakenDate

      fieldA

      fieldC

      'def' & RowNo() as DateLink


      Table C:

      ---------------------------------

      incidentDate

      closedDate

      fieldD

      fieldE

      'ghi' & RowNo() as DateLink

       


      I have simply concatenated tables B and C to table A as they contain mostly the same fields with some extra fields on one or 2 tables.


      After concatenating, I had the below:



      [Fact Table]:

      ---------------------------------

      incidentDate

      createdOnDate

      callTakenDate

      closedDate

      fieldA

      fieldB

      fieldC

      fieldD

      fieldE


      I then created a Calendar Link Table as follows:


      [Cal Link]:

      Load

      'Incident Date'            as [Date Type],

      [IncidentDate]            as [Date],

      [DateLink]

       

      Resident [Table A];


      ====================================


      Concatenate([Cal Link])

      Load

      'Call Taken Date'      as [Date Type],

      [callTakenDate]        as [Date],

      [DateLink]

       

      Resident [Table A];

       

       

      ====================================


      Concatenate([Cal Link])

      Load

      'Closed Date'       as [Date Type],

      [closedDate]        as [Date],

      [DateLink]

       

      Resident [Table A];

       

      ======================================================================

      I then fed Date to my calendar. See below.


      MinMax:

      Load

           //add your datefield to both min and max

           Min([Date])  as  MinDate,

           Max([Date]) as  MaxDate

       

       

      //the table where your date field is located

      Resident [Accident Reporting Data];

       

       

      LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));

      LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));

      LET vToday = $(vMaxDate);

       

       

      TempCal:

      Load

           Date($(vMinDate)+RowNo() -1) as TempDate

           AutoGenerate

           $(vMaxDate) - $(vMinDate)+1;

          

      DROP Table MinMax;

       

       

      // $(vMinDate) + Iterno()-1 As Num, 

      //                Date($(vMinDate) + IterNo() - 1) as TempDate 

      //                AutoGenerate 1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);



      MasterCalendar:

      LOAD

       

           TempDate as [Date],

           Week(TempDate) as [Week],

           Year(TempDate) as [Year],

           Month(TempDate) as [Month],

           Day(TempDate) as [Day],

           Weekday(TempDate) as [WeekDay],

           Day(TempDate) & '-' & Month(TempDate) as [DayMonth],

           QuarterName (TempDate,4) as [Quarter],

           IF (LEN(TempDate) > 1,'Q' & CEIL(Month(Addmonths(TempDate,-3))/3)) as [Financial_Quarter],

        IF(Month(TempDate) < 5,Year(AddMonths(TempDate,-3))+ 1,YEAR(TempDate)+ 1) as [Financial_Year]

       

      RESIDENT TempCal

      ORDER BY TempDate ASC;

       

      DROP TABLE TempCal;

       

       

      ---------------------------------------------------------------------------------------------------------------------------

      This doesn't seem to work however. When making selections in the [Date] field, it seems to work, but when trying to make individual selections in [Year], [Month] and [Day], it doesn't reflect accross my charts.

       

      Also, in the [Date] field, the same dates are repeated over and over (I'm assuming its one for each date field that was concatenated).


      Can someone please help me out and refer me to a better method or a way to fix this?