Business Rule - Allocation Script Logic - Not Working! Please Help.
Hello,
I need some help with the allocation script logic that I am working on.
Requirement:
We have a data form on which everything in POV, Page, and Rows & Columns has Level 0 members EXCEPT one Dimension (Product) in Page. Currmonth -> Sep. The user will enter forecast values at parent level (Product A and Product B) and the script should be such that it spreads down to level 0 members of Product A/Product B.
Below is the basic script that I could come up with – if I am not wrong this script should spread the input value among all those level 0 product members that already had values in that intersection and it would avoid considering the #Missing cells while calculating @COUNT value.
Example : The user entered 200 on the data form for a particular intersection (Product A is selected in Page) and the Product A has total 100 level 0 product members but only 2 (P1 and P2) out of those are populated with a value. So, shouldn’t the result be something like 200/2 = 100 and P1 and P2 should be assigned a value of 100 each??? Unfortunately, it is not working.
SET UPDATECALC ON;
FIX(“AccountMember”,”FY11”,”EntityMember”,”Local”,”Working”,”BU”,@LEVMBRS(“Product A”,0),@LEVMBRS(“Product B”,0),”Current”,@LEVMBRS(“Dept”,0),LEVMBRS(“Customer”,0))
&Currmonth
(IF(@ISIDESC(“ProductA”))
&Currmonth = &Currmonth->”Product A”/@COUNT(SKIPMISSING,@RANGE(“Product A”,@LEVMBRS(ProductA,0)));
ELSE
&Currmonth = &Currmonth->”Product B”/@COUNT(SKIPMISSING,@RANGE(“Product B”,@LEVMBRS(ProductB,0)));
ENDIF;
)
ENDFIX;
Please let me know your ideas on the above script and possible reasons why it would not be working.
Also, I need some change to the above logic. What I need is when the user enters a value of 200 on the data form for a particular intersection, it should spread that 200 value down to level 0 members proportionally based on the values already existing for level 0 members under that Product A/Product B.
Please let me know your ideas on how can I accomplish this. Looking forward for response.
Thank you!
~ Adella