Master Calendar

[shamitshah]

Hi,

I have the following script to create the master calendar and rolling data. Noticed that the date on the As Of Table is out by a day i.e first RecordDate AsOf comes out as 20/07/2011 instead of 21/07/2011

The other problem I have is that the running total does not seem to work in certain parts of the table

Not sure where I am going wrong.

Appreciate your assistance.

Temp:

load min(RecordDate) as MinDate,

     max(RecordDate) as MaxDate

Resident HISTORY;

Let vMinDate =Num( peek('MinDate',0,'Temp'));

Let vMaxDate =Num( peek('MaxDate',0,'Temp'));

Let vToday=$(vMaxDate);

TempCal:

LOAD

date($(vMinDate)+RowNo()-1) AS TempDate

AutoGenerate

$(vMaxDate)-$(vMinDate)+1;

DROP TABLE Temp;

MasterCalendar:

load

TempDate AS RecordDate,

Year(AddMonths(Date([TempDate]),6)) as FiscalRecordYear,

       Year(Date([TempDate])) as RecordYear,

       Month(Date([TempDate])) as RecordMonth, 

       MonthName(Date([TempDate])) as RecordMonthYr,

       'Q' & (mod(Ceil(Month(Date([TempDate]))/3)+1,4)+1) as RecordQuarter,

RESIDENT TempCal

ORDER BY TempDate ASC;

DROP TABLE TempCal;   

AsOfMonth:

load 'Current' as Type,

RecordDate as RecordDate_AsOf,

RecordDate

Resident MasterCalendar;

right join load RecordDate

Resident MasterCalendar;

Thanks

Shamit

[swuehl]

Have a look at this blog post:

The As-Of Table

Henric is doing a cartesian product between AsOf Date field and the field of the master calendar, then filter the AsOf table to only relations where AsOf date >= master calendar date.

In your script code, you are missing the cartesian product (and not sure if you want to filter or not).

I would suggest that your try to keep to Henric's blog.