Link 2 Date Fields in 1 Table to 1 Master Calendar

[twanqlik]

I have a table with 3 fields:

Example_Table:

Fact

Closed_Date

Respons_Date

When I filter on Closed_Date I want to see the sum of Fact.

And of course the same for the Response_Date.

The filtering on the date fields shouldn't of course interfere each other.

This works perfect with expressions like:

=Sum( {<Closed_Date>}  Fact)

=Sum( {<Respons_Date>}  Fact)

However, eventually I want to have 1 master calendar linked to both the Date Fields. The user should be able to filter on a specific date and that date should be selected in both the Date fields.

I think that for that I need to create a link table between my Example Table and the master calendar, however I'm struggling with that. Any tips?

*Ps, this is an example, when i know how to create this link table i will use it in a bigger dashboard

[Anil_Babu_Samineni]

PFA

                                                            ***** Script *****

Sample:

LOAD Fact,

     Closed_Date

FROM

[2_Date_Fields.xlsx]

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

Sample1:

LOAD Fact,

     Respons_Date

FROM

[2_Date_Fields.xlsx]

(ooxml, embedded labels, table is Sheet1);

CalendarMaster:

LOAD

    Date(Closed_Date) AS Closed_Date;

Load Date(MinDate + IterNo() -1 ) AS Closed_Date While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load

    Min(Closed_Date) AS MinDate,

    Max(Closed_Date) AS MaxDate

RESIDENT Sample;

LOAD

    Date(Respons_Date) AS Respons_Date;

Load Date(MinDate + IterNo() -1 ) AS Respons_Date While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load

    Min(Respons_Date) AS MinDate,

    Max(Respons_Date) AS MaxDate

RESIDENT Sample1;

[twanqlik]

Thanks, but i don't want to split the 'example table' into 2 tables.

My table in my dashboard has many more fields than only  'Fact' 

[MK_QSL · Accepted solution]

Use something like below.

FactTable:
LOAD Fact, 
     Closed_Date, 
     Respons_Date
FROM
[https://community.qlik.com/servlet/JiveServlet/download/1433301-314246/2_Date_Fields.xlsx]
(ooxml, embedded labels, table is Sheet1);

LinkTable:
Load Distinct Fact, Closed_Date as LinkDate, 'Closed' as Flag Resident FactTable;
Load Distinct Fact, Respons_Date as LinkDate, 'Respons' as Flag Resident FactTable;

TempMinMaxDate:
Load 
 Min(LinkDate) as MinDate,
 Max(LinkDate) as MaxDate
Resident LinkTable;

Let vMinDate = Num(Peek('MinDate',0,'TempMinMaxDate'));
Let vMaxDate = Num(Peek('MaxDate',0,'TempMinMaxDate'));

Drop Table TempMinMaxDate;

Calendar:
Load 
 Date(TempDate) as LinkDate,
 Week(TempDate) as Week,
 Month(TempDate) as Month,
 'Q' & Ceil(Month(TempDate)/3) as Quarter,
 Year(TempDate) as Year
;
Load 
 $(vMinDate) + IterNo() - 1 as TempDate 
AutoGenerate 1 
While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

Use flag in your expressions to distinguish between Closed and Response date.

[ss2q]

How about if I have two tables and each table has one date which is different from the other? For example, the Order table has the Ordered date and the Invoice table has GL_date and I want to only take the year from the date and use it. For that how will the code be? I will be grateful if you can help

[ss2q]

How about if I have two tables order lines and invoice? I have ordered the date in order line table and gl_date in the invoice table. I have already extracted from this each date in each table because I want to make an analysis using Year. So, how to relate the year of order date and year of invoice date