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?
