Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
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
In which table you want it to link actually ?
financials
and it should be end of each month only
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)
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 |
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
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
Ate,
Here's an example I prepared based on your data.