Development on Rolling Period - Creating future periods doesn't exist in data loading

[caccio88]

Hi everyone,

I'm trying to implement something that I can't find as a solution in the community relative to the great post of gwassenaar‌, Calculating rolling n-period totals, averages or other aggregations.

What I want to do is to have even the future period that not exists in the data loading depending on the "Rolling Period" chosen.

I mean that if I have this state loadment

SalesData:

LOAD * INLINE [

    Date, Item, Amount

    01/12/2014, A, 5

    01/11/2014, A, 10

    01/01/2015, B, 10

    ];

My target will be not only to display the Sum of the 3 previous MonthYear for every existing MonthYear, like that

or

but even feb-15=15 and mar-15=10.

Is it clear my goal? Do you think that could be possible?

gwassenaar‌ stalwar1‌ swuehl‌

Thank you.

Filiberto

[Gysbert_Wassenaar]

Certainly. Just adjust the way you generate the AsOfMonth table:

AsOfMonth:

load

Month + IterNo() -1 as Month_AsOf,

Month

Resident SalesData

while IterNo() <= 3;

[caccio88]

Thanks gwassenaar‌ for your help.

I think that yuor answer works when you don't have to manage changes of year. True?

Cause when 2014 year goes to 2015 year the "IterNo() -1" doesn't work.

As you can see in my  qvw example attached I would like to solve that type of issue.

[Gysbert_Wassenaar · Accepted solution]

Yes and no. If you're not working with month numbers but dates then you use the AddMonths function.

SalesData:

LOAD Date(Date#(Date,'DD/MM/YYYY'),'DD/MM/YYYY') as Month,Item, Amount INLINE [

    Date, Item, Amount

    01/12/2014, A, 5

    01/11/2014, A, 10

    01/01/2015, B, 10

    ];

AsOfMonth:

LOAD DISTINCT

    Date(AddMonths(Date, IterNo()-1),'DD/MM/YYYY') as Month_AsOf,

    Month

RESIDENT

    SalesData

WHILE

    IterNo() <=3

    ;

[sunny_talwar]

Updated your QVW file with Gysbert's ‌mentioned updates in case you were having trouble doing this (and in the process learnt a new way to create the AsOfTable myself.) Thanks Gysbert

AsOfMonth:

load 'Current' as Type,

YearMonthID as YearMonthID_AsOf,

YearMonth as YearMonth_AsOf,

DataMonthYear as DataMonthYear_AsOf,

YearMonthID as YearMonthID

Resident MasterCalendar;

Concatenate (AsOfMonth)

load 'Rolling 3' as Type,

YearMonthID as YearMonthID_AsOf,

YearMonth as YearMonth_AsOf,

Date(AddMonths(DataMonthYear, IterNo() - 1)) as DataMonthYear_AsOf,

YearMonthID as YearMonthID

Resident MasterCalendar

while IterNo() <= 3;

[caccio88]

Thank you so much.