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

[sibideepak]

Hi laniefran,

Can you little elaborate your requirement ??

[#3]

hi deepak

i cant link my master link to my data

[#4]

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;

[sibideepak]

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.

[Gabriel]

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

[jpenuliar]

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.

JP

[#8]

cannot find CommonDate

[sibideepak]

Use TempDate instead CommonDate in Gabsus04 example

[#10]

which thread po