How to bulid WHERE statement in transformation between two cubes

33 Views

Hi,

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.

Regards,

Pat