How to solve a dependant rolling calc

How to solve a dependant rolling calc

kurt
kurt

March 30th, 2017, 1:59 pm #1

Hello all I have a case where a prior month total needs to roll into a base member of the current month total, then roll the logic forward into future periods. I'm running into an order of operations issue. I can't use two-pass because I'm calculating for members of non-account dim


I have two dims:

Source:
---TotalSource
-----S1
-----S2

Initiative:
---TotalInitiative
-----I1
-----PriorBase

So I want to calculate for "PriorBase" something basically like this: PriorBase = @Prior(TotalInitiative->TotalSource);

The problem I'm running into is that Essbase is calculating period before PriorBase is calculated. I've worked around this issue by using LOOP (or running the calc 12 times!) but would hate to resort to that. Any suggestions?

Thanks!
Quote
Share

Pete
Pete

April 1st, 2017, 9:54 pm #2

Hey,

thought about making TotalInitiative a dynamic calc and then wrapping the calc script in interdependancy brackets.

Stolen from the DBAG
https://docs.oracle.com/cd/E57185_01/ED ... devcs.html

If you are using an IF statement or an interdependent formula, enclose the formula in parentheses to associate it with the specified member.

For example, the following formula is associated with the Commission member in the database outline:

Commission
(IF(Sales < 100)
Commission = 0;
ENDIF;)
Quote
Share

Amit
Amit

April 2nd, 2017, 2:20 pm #3

Hello all I have a case where a prior month total needs to roll into a base member of the current month total, then roll the logic forward into future periods. I'm running into an order of operations issue. I can't use two-pass because I'm calculating for members of non-account dim


I have two dims:

Source:
---TotalSource
-----S1
-----S2

Initiative:
---TotalInitiative
-----I1
-----PriorBase

So I want to calculate for "PriorBase" something basically like this: PriorBase = @Prior(TotalInitiative->TotalSource);

The problem I'm running into is that Essbase is calculating period before PriorBase is calculated. I've worked around this issue by using LOOP (or running the calc 12 times!) but would hate to resort to that. Any suggestions?

Thanks!
Have you tried @CALCMODE (BOTTOMUP)? (EOM)
Quote
Share

kurt
kurt

April 3rd, 2017, 3:18 pm #4

Hey,

thought about making TotalInitiative a dynamic calc and then wrapping the calc script in interdependancy brackets.

Stolen from the DBAG
https://docs.oracle.com/cd/E57185_01/ED ... devcs.html

If you are using an IF statement or an interdependent formula, enclose the formula in parentheses to associate it with the specified member.

For example, the following formula is associated with the Commission member in the database outline:

Commission
(IF(Sales < 100)
Commission = 0;
ENDIF;)
Thanks for the suggestion, I was trying not to use dynamic calcs because of other calc dependencies that may slow things down but I'll give it a shot.
Quote
Share

kurt
kurt

April 3rd, 2017, 3:19 pm #5

I had tried that but didn't work. I'm using a loop for now. Sort of a hack but it works and runs fast in this case
Quote
Share

GLennS
GLennS

April 3rd, 2017, 6:35 pm #6

Try using cell mode. IT will process each of the members in a fix one at a time instead of all at once
Quote
Share


Confirmation of reply: