Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

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

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?


1 Solution

Accepted Solutions
reddys310
Honored Contributor II

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

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

1 Reply
reddys310
Honored Contributor II

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

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