Here you go :
// Your parameters
Let vNbMonthsToLetAsItIs=6;
let vNbOfMonthsToTakeInAvg=6;
// Your data load
Temp_Data1:
LOAD
"Month" as Period,
Cost
FROM [lib://CommunityTests/RollingAvg.xlsx]
(ooxml, embedded labels, table is Feuil1);
// Rest of script
Temp_Data2:
LOAD
recno() as PeriodID,
Period,
Cost
resident Temp_Data1
order by Period;
join(Temp_Data2)
LOAD
recno() as PeriodID_2,
Cost as Cost_2
resident Temp_Data1
order by Period;
Drop table Temp_Data1;
NoConcatenate
Temp_Data3:
LOAD
*
resident Temp_Data2
where PeriodID>=PeriodID_2-$(vNbOfMonthsToTakeInAvg) and PeriodID<=PeriodID_2;
Drop table Temp_Data2;
NoConcatenate
Final:
Load
avg(Cost) as Cost,
Max(Period) as Period,
PeriodID_2
resident Temp_Data3
where if(PeriodID_2<=$(vNbMonthsToLetAsItIs),PeriodID_2=PeriodID,1=1)
group by PeriodID_2;
Drop table Temp_Data3;
