I have a perfectly fine script for Rolling N Months using As-Of-Table. I now need to make it into Rolling Weekly corresponding to the Rolling N Months since data will now be available on a weekly basis instead of Monthly basis.
For eg. In july 2nd week, it should rolling 3 months from july 2nd week.
Please help.
Below is my existing script.
Temp: Load Distinct MonthStart(Date(MakeDate(Year,Month),'YYYYMM')) as Period1, YrMnth Resident CHC_Data;
// Temporary table with list of Period values PeriodTbl: LOAD Date(FieldValue('Period1',RecNo()),'YYYYMM') as Period AUTOGENERATE FieldValueCount('Period1');
AsOfPeriodTable:
//Load every month as current month into AsOf table LOAD Period as AsOfPeriod, Month(Period)& Right(Year(Period),2) as MONTHYR, Month(Period) as MONTH, 'Monthly' as [PeriodType ], Period as Period1, Year(Period) as YEAR RESIDENT PeriodTbl ;
CONCATENATE (AsOfPeriodTable) //Load Rolling 6 into AsOf table LOAD Period as AsOfPeriod, Month(Period)& Right(Year(Period),2) as MONTHYR, Month(Period) as MONTH, 'Rolling 6' as [PeriodType ], Date(AddMonths(Period,1-IterNo()),'YYYYMM') as Period1, Year(Period) as YEAR RESIDENT PeriodTbl WHILE IterNo() <= 6;
CONCATENATE (AsOfPeriodTable) //Load Rolling 3 into AsOf table LOAD Period as AsOfPeriod, Month(Period)& Right(Year(Period),2) as MONTHYR, Month(Period) as MONTH, 'Rolling 3' as [PeriodType ], Date(AddMonths(Period,1-IterNo()),'YYYYMM') as Period1, Year(Period) as YEAR RESIDENT PeriodTbl WHILE IterNo() <= 3;
INNER JOIN (AsOfPeriodTable) LOAD Period as Period1 RESIDENT PeriodTbl;
