MDX Script - Infinit Recursion Detected

I'm getting the error message: "Infinit Recursion Detected. The loop dependencies is: 98 -> 99"

I have this script in Cube Calculations, in this order:

[account dimension].[account hierachy].[bank application] = IIF( [account dimension].[account hierachy].[Asset]>[account dimension].[account hierachy].[Liability], [account dimension].[account hierachy].[Asset]-[account dimension].[account hierachy].[Liability], 0 );

[account dimension].[account hierachy].[bank loan] = IIF( [account dimension].[account hierachy].[Asset]<[account dimension].[account hierachy].[Liability], [account dimension].[account hierachy].[Asset]-[account dimension].[account hierachy].[Liability], 0 );

"bank application" is children of "asset" account and "bank loan" is children of "liability". How can I fix that?

To calculate [bank application], you need the value of [Asset]. But [Asset] is the parent of [bank application], in the simplest case, the value of [Asset] is the sum of the values of all children which include [bank application], hence the recursion. A simplistic fix is to use the ~ unary operators to exclude [bank application] from contributing to the value of [Asset]. I would examing the hierarchy and calculations to see if they make sense in the first place.|||

It's a little bit difficult to say how to fix it without knowing a little bit more about your requirements.

But with the following assumptions:

that your account hierarchy is parent child|||

Yes, account hierarchy is parent child, I can't move the calculated members to another position but I want that "bank application" or "bank loan" rolling up to their parent.

I don't know in other countries, but in Brazil for oficial balance reports the assets and lialbility must have the same value, because the liability aggregates the profits and let say that: it's a convention here.

My application is a budget application, then the values are planning values and for simplicity we make asset and liability have the same value putting the diference (asset - liability) in the cash application or if the value is positive in the "bank application" and if the value is negative in the "bank loan" account.

That is my case, If the diference of asset and liability is positive I need to put these diference in "bank application" and if the diference is negtive I put the value in the "bank loan". Then the value rollup to their parent and asset and liability account will always have the same value.

With freeze I can do that kind of thing? I need to break the recursion, make the recursion run only one time.

|||

Yes the Freeze works fine!

Thanks!