Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
twanqlik
Creator II
Creator II

Link 2 Date Fields in 1 Table to 1 Master Calendar

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

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

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.

View solution in original post

5 Replies
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;

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
twanqlik
Creator II
Creator II
Author

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
MVP
MVP

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
Contributor III
Contributor III

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
Contributor III
Contributor III

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