common date

[#1]

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

RepMonth	Snapshot Month	Cust_Month	Fin_Month
31-Dec-14	12/31/2014	12/31/2014	12/31/2014
31-Jan-15	01/31/2015	01/31/2015	01/31/2015
28-Feb-15	02/28/2015	02/28/2015	02/28/2015
31-Mar-15	03/31/2015	03/31/2015	03/31/2015
30-Apr-15	04/30/2015	04/30/2015	04/30/2015
31-May-15	05/31/2015	05/31/2015	05/31/2015
30-Jun-15	06/30/2015	06/30/2015	06/30/2015
31-Jul-15	07/31/2015	07/31/2015	07/31/2015
31-Aug-15	08/31/2015	08/31/2015	08/31/2015
30-Sep-15	09/30/2015	09/30/2015	09/30/2015
31-Oct-15	10/31/2015	10/31/2015	10/31/2015
30-Nov-15	11/30/2015	11/30/2015	11/30/2015
31-Dec-15	12/31/2015	12/31/2015	12/31/2015

[#2]

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;

[jpenuliar]

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]

In which table you want it to link actually ?

[#5]

financials

[#6]

and it should be end of each month only

[jpenuliar]

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)

[#8]

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_Month	Fin_Month	Snapshot Month	CommonDate	CommonMonth
02/28/2015	02/28/2015	02/28/2015	02/01/2015	Jan
02/28/2015	02/28/2015	02/28/2015	03/01/2015	Feb
02/28/2015	02/28/2015	02/28/2015	01/01/2015	Dec
02/28/2015	02/28/2015	02/28/2015	12/31/2014
02/28/2015	02/28/2015	02/28/2015	01/31/2015
02/28/2015	02/28/2015	02/28/2015	02/28/2015

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

Cust_Month	Fin_Month	Snapshot Month	CommonDate	CommonMonth
02/28/2015	02/28/2015	02/28/2015	15-Feb-28	Feb

[sibideepak]

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]

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]

Ate,

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