Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I would like to create an asof calendar using date field.
I need to create dimensions for 12 rolling months, previous year.
This is my code but there is something wrong i thought:
LOAD
Date as AsOfDate
// Period as AsOfPeriod
,'Current' as PeriodType
,Date
,Year(Date) as Anno
,Date(Date,'YYYYMM') as YearMonth
RESIDENT CALENDARIO;
CONCATENATE (AsOfPeriodTable)
//Load Rolling 3 into AsOf table
LOAD
Date as AsOfDate
,'Rolling 12' as PeriodType
,date(addmonths(Date,1-iterno())) as Date
,Year(Date) as Anno
,Date(Date,'YYYYMM') as YearMonth
RESIDENT CALENDARIO
WHILE iterno() <= 12;
CONCATENATE (AsOfPeriodTable)
//Load Rolling 6 into AsOf table
LOAD
Date as AsOfDate
,'PreviousYear' as PeriodType
,date(AddMonths(Date,-12)) as Date
,Year(Date) as Year
,Date(Date,'YYYYMM') as YearMonth
RESIDENT CALENDARIO
;
Right JOIN (AsOfPeriodTable)
LOAD Date
RESIDENT CALENDARIO;
Hi, Carlo!
What is the problem of your code? Is there a error during execution or is the result wrong?
Carlo,
Please see the attachment.
Thanks,
AS
This example is useful for previous year and previous period, but how can i create the rolling on this?
Thank you
Hi Carlo,
Examples of rolling are "Last" flags.
%Flag_LastMonth_M01 If Date=6th Jan 2014 would select KeyDate's 6th Dec 2013 to 6th Jan 2014
For a rolling year you would use %Flag_LastMonth_M12
Regards
Richard