Moving Total in PP

[ykud]

Any suggestions how to calculate moving total in PP?

Got typical sales data.
{Date ,Product, Quantity}
Need to calculate moving-total(Quantity, 30 for Product) in terms of RN, this meaning all the sales for previous 30 days.
Can even throw Product away in this example.

Desired Result Table:
Date   Quantity  Moving-Total
01.01      10            10
02.02      12            22
...................................
30.01      10            200
31.01       5             200 - 10 + 5 = 195
etc

Any ideas, anyone? PP or Transformer.



   

[Draoued]

In this case, I would go for a special drill-down path in the Time dimension with calculated categories


Running Previous 30 days
                          1 day
                          2 days
                          .....
                          .....
                          29 days
                          30 days


All the 1 day to 30 days are calculated categories , so it will be possible to see only the Previous 30 days running total , but that would be available for all measures.
The bad points are : you can not expand it to a bigger period without changing your model
and you can not compare Quantity to the Moving total.

[ykud]

In this case, I would go for a special drill-down path in the Time dimension with calculated categories

All the 1 day to 30 days are calculated categories , so it will be possible to see only the Previous 30 days running total , but that would be available for all measures.

I just do not seem to get that idea. As far as I understand:
Create new calculated colums Day 30 = Current_day, Day 29 = Current_day - 1, and etc.
Create a dimesion with Rolling total as an Rollup to these categories -- Can't do that.

Please, explain it in a more detailed variant?

[varshams]

I am facing the following problem


Year         Month          Initial Qty            Closed Qty          Open Qty
2006        Jan               10                        8                        2
2006        Feb                 5                       3                         4
2006        Mar               20                      18                        6


Now the aggregate open qty for year 2006 should be 6 and not ( 6+2+4)

[Draoued]

In Open Qty Measure properties , change the aggration for TIME dimension to be LAST Period instead of Sum

[varshams]

I used the accumulated measure. its working.
only problem is i cannot have nested categories in time dimension
bcos for nested categories (year->quarter->month), accumulation will not give the correct results