So I’ve been working on a 20,000+ line business rule in Planning. Yes, for real. It’s not a complicated script, it’s just repetitive due to using values from a Smart List. It’s quite simple – depending on the month and the cost center they will be moving to, copy over the employee details for the remainder of the year to the new cost center while retaining values in the old cost center up to the point of transfer. However, the script would keep wiping the data. I knew it was working because when I tested only for copying of data, I had values in both cost centers. However, when I added the second section to clear the data in the old cost center, the entire year’s data was wiped, not just the old cost center data months. How frustrating.
Here’s what I discovered…
Here is my test worksheet. The bottom part of the sheet has the original data. The top is the new cost center that data is supposed to copy to.
Here is my original script. Note that if the conditions are met, 2 actions are to take place.
However, when I run the script, all monthly data gets wiped. You can see that my TransferMonth and TransferToCC values are still intact.
I had a hypothesis that it was running the second action even if the conditions were not met. To test, I replaced the #Missing with 412. Sure enough, it was happening.
However, when I removed the second action from the IF/ELSEIF statement, it ran just fine. (Note that I separated out both actions to different scripts and it worked perfectly.)
So, in short, I had to write double the code to get the data to copy and then to clear from the old cost center.
So let me pose a question…does Essbase take a second action item in an IF/ELSEIF statement to be an ELSE statement instead? From my research, it appears that way, but I couldn’t find anything definitive in the Essbase Tech Ref. The closest I found was how Essbase treats IF versus ELSE calculations (that it calculates for the IF/ELSEIF items before the ELSE), but nothing about multiple items below an IF/ELSEIF statement.
Moral of the story? Only one action item per IF/ELSEIF condition set.
RealTriGeek 
I am making an assumption that Once the Begbalance->Transfer month == 1 is fulfilled the employee needs to be transferred and second condition is just to check which entity the data needs to be transferred to. Have you tried creating an IF within IF condition
Eg. IF( Begbalance->Transfer month == 1)
IF(Begbalance->ToCC==1)
XXXX=&VarEntity
ELSEIF(Begbalance->ToCC==2)
XXXX=&VarEntity
ENDIF
&VarEntity=#missing
ELSEIF( Begbalance->Transfer month == 2)
IF(Begbalance->ToCC==1)
XXXX=&VarEntity
ELSEIF(Begbalance->ToCC==2)
XXXX=&VarEntity
ENDIF
&VarEntity=#missing
ENDIF
In this case it should finish the “if” conditions before clearing the data.You might have already tried this option. Let me know your thoughts.
Hi Neha – Yes, I have tried this and got the same results, unfortunately. Thanks for the suggestion!
It’s not that you can only do one operation per IF/ELSEIF, it’s that (I’m speculating here) both of your statements are being executed “simultaneously”, not sequentially as you might imagine. The following calc script from Sample.Basic illustrates what’s going on. If you set Florida to 1 and run it, both New York and Massachusetts get updated with the correct values. If you set Florida to 2, both Massachusetts and Connecticut end up as #MI no matter what value is originally in Connecticut. If you set Florida to 3 then both Massachusetts and Connecticut are set to 500 even if Connecticut is not 500 to begin with. Very quirky IMHO…
//ESS_LOCALE English_UnitedStates.Latin1@Binary
SET UPDATECALC OFF;
SET AGGMISSG ON;
FIX(“Jan”,”COGS”,”100-10″){
“Actual”(
IF(“Florida” == 1)
“New York” = “Connecticut”;
“Massachusetts” = “New Hampshire”;
ELSEIF(“Florida” == 2)
“Massachusetts” = “Connecticut”;
“Connecticut” = #MISSING;
ELSEIF(“Florida” == 3)
“Massachusetts” = “Connecticut”;
“Connecticut” = 500;
)
}
I’m glad you were able to replicate this! And your thought about the actions being run simultaneously is very interesting…
Tim…I think you are onto something. I put in a third action item and it appears these items are running simultaneously as, logically, the calc script doesn’t tell it what to do what it is doing. However, when you mash them all together, it makes sense.
That can’t be right. Do you get more specific later on about which months to copy, based on the value of TransferMonth? Are you equally specific about which months you clear? From what I can see, I’d lay it at the doorstep of not FIXing on Period. (Whatever it is, Essbase understands how to do an IF with multiple lines, for sure.)
I have had it it fixing on period and not in the real script as the months it clears is very specific and got the same results. I have tried nested if statements and got the same results.