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

Tue, 26 Jan 2016 18:46:51 GMT

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?

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

reddy-s — Tue, 26 Jan 2016 19:10:34 GMT

Hi Deli,

When you have multiple date fields and you have to draw a logical deduction between all of these you need to make use of a canonical Calander.

Check this article, this should help solve your issue:

Canonical Date

Thanks,

Sangram

topic Single fact table with 4 date fields - Single Calendar (QSense) in App Development

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

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