topic common date in QlikView

common date

Thu, 19 Mar 2015 13:44:59 GMT

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

Re: common date

sibideepak — Thu, 19 Mar 2015 13:50:57 GMT

Hi laniefran,

Can you little elaborate your requirement ??

Re: common date

Thu, 19 Mar 2015 13:54:12 GMT

hi deepak

i cant link my master link to my data

Re: common date

Thu, 19 Mar 2015 13:54:46 GMT

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;

mastercalendar:
Load
'Common' as DateType,
Date('YY-MMM-DD') as CommonDate,
Year(CommonDate) as CommonYear,
Month(CommonDate) as CommonMonth
Resident tempcal;
Drop Table tempcal;

Re: common date

sibideepak — Thu, 19 Mar 2015 14:02:23 GMT

Hi laniefran,

Try to make your data model with either star schema or snow flake schema ,

And try to make all dates to be in one or two table and assign those Dates as CommonDate.

Re: common date

Gabriel — Thu, 19 Mar 2015 14:08:46 GMT

Hi,

I think you need a full master calendar for your CommonDates.

Update your calendar tab with this

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

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

Hopefully that will give you full calendar linking

Re: common date

jpenuliar — Thu, 19 Mar 2015 14:09:54 GMT

Ate Lanie,

have a look at this Thread to first generate a Master calendar using a Script.

then go back to the common Calendar Tutorial how to link the SnapshotMonths,Fin_Months & Cust_Month to the Master Calendar.

Re: common date

Thu, 19 Mar 2015 14:16:11 GMT

cannot find CommonDate

Re: common date

sibideepak — Thu, 19 Mar 2015 14:18:36 GMT

Use TempDate instead CommonDate in Gabsus04 example

Re: common date

Thu, 19 Mar 2015 14:18:55 GMT

which thread po

Re: common date

Thu, 19 Mar 2015 14:26:44 GMT

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;

Re: common date

jpenuliar — Thu, 19 Mar 2015 14:31:51 GMT

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

Re: common date

sibideepak — Thu, 19 Mar 2015 14:33:50 GMT

In which table you want it to link actually ?

Re: common date

Thu, 19 Mar 2015 14:35:15 GMT

financials

Re: common date

Thu, 19 Mar 2015 14:37:53 GMT

and it should be end of each month only

Re: common date

jpenuliar — Thu, 19 Mar 2015 14:38:40 GMT

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)

Re: common date

Thu, 19 Mar 2015 14:46:54 GMT

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

Re: common date

sibideepak — Thu, 19 Mar 2015 14:51:38 GMT

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

Re: common date

rwunderlich — Thu, 19 Mar 2015 15:06:33 GMT

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

Re: common date

jpenuliar — Thu, 19 Mar 2015 15:19:16 GMT

Ate,

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