topic Re: Development on Rolling Period - Creating future periods doesn't exist in data loading in QlikView

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

caccio88 — Sat, 07 May 2016 05:35:58 GMT

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

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

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

Gysbert_Wassenaar — Sat, 07 May 2016 05:40:14 GMT

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

AsOfMonth:

load

Month + IterNo() -1 as Month_AsOf,

Month

Resident SalesData

while IterNo() <= 3;

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

caccio88 — Sat, 07 May 2016 06:03:25 GMT

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.

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

Gysbert_Wassenaar — Sat, 07 May 2016 06:38:25 GMT

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

;

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

sunny_talwar — Sat, 07 May 2016 09:08:49 GMT

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;

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

caccio88 — Sat, 07 May 2016 09:35:09 GMT

Thank you so much.