Rolling 12 month Average (stock balance)

[#1]

Hi, I have problems to create the rolling 12 month average in QV. Have attached a docuement what I have from from datawarehouse and what I need to calculate in QV.

In me report I cant get det correct values in the rolling 12 avergae in september becasue I only get the change for the periods in 2010 and not the ing balance for 2010. I think I need to create an out going balance column in QV first but I dont now How - I only have values in jan each year?

Regards mikael

[#2 · Accepted solution]

Lets say that you need to build the rolling qty per product while you read the initial table you should build

an accumulation key like the following one in order to build break levels:

Load company & '|' & product & '|' & Year AS AccumulationKey, PeriodQty as Qty_trans...

Then you sould reference to that resident table and build opening and closing figures with the following technique:

FinalStock:

LOAD

product,

     Year,   Month,  AccumulationKey, Qty_trans,

if(rowno() = 1 OR AccumulationKey <> peek(AccumulationKey), 0, peek(ClosingQty)) AS OpeningQty,

if(rowno() = 1 OR AccumulationKey <> peek(AccumulationKey), Qty_trans, peek(ClosingQty) + Qty_trans) AS ClosingQty,

        if(rowno() = 1 OR AccumulationKey <> peek(AccumulationKey), Qty_Weight, peek(ClosingWeight) + Qty_Weight) AS ClosingWeight,

RESIDENT

TempSum

;

On the above code for the 1st row the opening amount is 0 and whenever there is a break level

on accoumulation key the opening amount is set to 0 again. Into the same level period qty is accumulates to closing quantity which becomes the opening qty of the next month.

[#3]

Hi, Thank you for the suggestion and it works fine for me / regards mikael