I have inventory cube that stores receipts(0CPTSTREBU) by week (0CALWEEK). I want to move this data to a new cube that will have fixed week buckets (example - week1, week2, week3....etc).
Now my requirement is - when I move the the receipt quantity to new cube say in
1. bucket1/week1 - I want to restrict it by 0CALWEEK = current week
2. bucket2/week2 - I want to restrcit it by 0CALWEEK = current week -1...etc.
that means for week1, get receipt qty where 0CALWEEK = current week, for week2, get receipt qty where 0CALWEEK = current week-1 and so on.
Can I use transformation formula to achieve this? If yes, how?
Note: I tried using this in BEX, but ended with performance issue. So, trying to see if if can be achieved at cube level.