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.
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.
Quote from: Draoued on 23 Nov 2005 02:17:31 AM
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?
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)
In Open Qty Measure properties , change the aggration for TIME dimension to be LAST Period instead of Sum
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