Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

common date

Newbie needing help here.. been trying to solve this for days.. using the canonical dates tutorial..

mine is different though as all of the dates should show up the same

I have 4 dates

Fin_Month

Cust_Month

Snapshot_Month

People_Month

tried to create a tempcal and master calendar.. but its isolated from the rest of my data

i would like to just select Snapshot Month and it should automatically select the same months as well

RepMonthSnapshot MonthCust_MonthFin_Month
31-Dec-1412/31/201412/31/201412/31/2014
31-Jan-1501/31/201501/31/201501/31/2015
28-Feb-1502/28/201502/28/201502/28/2015
31-Mar-1503/31/201503/31/201503/31/2015
30-Apr-1504/30/201504/30/201504/30/2015
31-May-1505/31/201505/31/201505/31/2015
30-Jun-1506/30/201506/30/201506/30/2015
31-Jul-1507/31/201507/31/201507/31/2015
31-Aug-1508/31/201508/31/201508/31/2015
30-Sep-1509/30/201509/30/201509/30/2015
31-Oct-1510/31/201510/31/201510/31/2015
30-Nov-1511/30/201511/30/201511/30/2015
31-Dec-1512/31/201512/31/201512/31/2015
23 Replies
Not applicable
Author

thanks no more error.. but still unlinked

tempcal:

Load
Date([Snapshot Month]) as CommonDate
Resident weekly_outstanding;
Concatenate
Load
Date(Fin_Month) as CommonDate
Resident financials;
Concatenate
Load
Date(Cust_Month) as CommonDate
Resident customer_actuals;


Temp:
LOAD
Min(CommonDate) as MinDate,
Max(CommonDate) as MaxDate
Resident tempcal;

Let varMinDate = Num(Peek('MinDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('MaxDate', 0, 'Temp'));

TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

mastercalendar:
Load
Date(TempDate,'YY-MMM-DD') as CommonDate,
Year(TempDate) as CommonYear,
Month(TempDate) as CommonMonth
Resident TempCalendar;
DROP Table TempCalendar;

Capture.PNG

jpenuliar
Partner
Partner

Hi Lanie,

There's plenty of tutorial/samples to generate Calendar using diff methods,

I believe this link (autogenerate using Min/Max dates )explains it better :

http://community.qlik.com/docs/DOC-1310

you can define Start and End dates manually or derive from your data using Min/Max functions

sibideepak
Creator II
Creator II

In which table you want it to link actually ?

Not applicable
Author

financials

Not applicable
Author

and it should be end of each month only

jpenuliar
Partner
Partner

You will have to create a Link table or Bridge table from this tutorial:

http://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions/

all your three Date fields are linked to the Link table then you link table is linked to

the Master Calendar (Common)

Not applicable
Author

i did this..

mastercalendar:

Load
Date(monthend(TempDate),'YY-MMM-DD') as CommonDate,
Year(TempDate) as CommonYear,
Month(TempDate) as CommonMonth
Resident TempCalendar;
DROP Table TempCalendar;

but still my dates are not linked.. and if I try to select all the dates... the common date still looks odd

Cust_MonthFin_MonthSnapshot MonthCommonDateCommonMonth
02/28/201502/28/201502/28/201502/01/2015Jan
02/28/201502/28/201502/28/201503/01/2015Feb
02/28/201502/28/201502/28/201501/01/2015Dec
02/28/201502/28/201502/28/201512/31/2014
02/28/201502/28/201502/28/201501/31/2015
02/28/201502/28/201502/28/201502/28/2015

whereas... this is what I want...

Cust_MonthFin_MonthSnapshot MonthCommonDateCommonMonth
02/28/201502/28/201502/28/201515-Feb-28Feb
sibideepak
Creator II
Creator II

First add

Date(FinMonth,''YY-MMM-DD'') as CommonDate  as field in the financial table

and add

Where len(FinMonth)>0; in the financial table

Add

Drop table tempcal in the end

rwunderlich
MVP & Luminary
MVP & Luminary

You will need to make a DateLink Table that bridges a common key (such as LT) to the Calendar. See this example:

Qlikview Cookbook: Tutorial - Using Common Date Dimensions http://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions/

-Rob

jpenuliar
Partner
Partner

Ate,

Here's an example I prepared based on your data.