Question Complex formula to populate an element with the sum of all the other elements under the same parent.

[Warren Meads]

Hi Can any one help with the formula required for this. I am populating a specific element with an amount from another cube and want to subtract from this the value of all other elements under the same parent. This can not be done in the dimension hierarchy as the specific element populated is dynamically determined by data in the referenced cube and can be any one of the elements. The other elements to be summed in the same hierarchy have manual input on that particular row. We can see the value summed in the Parent but the feeders sequence populate this after the value in the specific element has been determined so do not have a summed value of the hierarchy before the specific element has been populated.

I can not see a function for the number of elements in a hierarchy or the last element position in the Hierarch. I have assumed that we can determine the position of "Current" element using POSITION("Time","Month List", Element("Dimension") ). I can determine the first position but need the last position. (ah while writing this I had a thought that perhaps I can create a dummy element at the end of the elements and use POSITION to determine this).

 

[Ben Hill]

Hi Warren,

I should mention is that we have CHILDCOUNT, PARENT, PARENTCOUNT functions which do not appear in the documentation (I'll have the team sort this out).
Usage Example:

CHILDCOUNT("Time","Default","2018")

Edit: These functions have been added to the doco here.

There are a couple of options depending on the circumstance.

1. Branching IF Statements
This will only work if you know the maximum number of children and that isn't going to change. This is possible by testing for each child in a branching IF statement and the CHILD function.

2. Hierarchy Structure to include a Subtotal
My preferred approach is to instead add a level to the hierarchy and structure it such that you can have a subtotal of the elements you are planning on netting off against.

3. Alternate Measure
One other option is to have a measure which populates only if it is not the element which is subject to a LINK to another cube. You can then use the parent reference (which is a relatively new feature) to return the parents value for the second measure.

Assuming it is an Account Dimension

["Account:Profit and Loss»@Parent","Measure 2"]

Here is an example of using the @Parent reference to get a days portion of its month parent.

["Monthly Revenue"]
*
([Δ"No Site", "Daily Seasonality"] / [Δ"No Site", "Daily Seasonality", "Date:Default»@Parent"])

Let me know if this helps

 

[Warren Meads]

Assuming it is an Account Dimension

["Account:Profit and Loss»@Parent","Measure 2"]

Here is an example of using the @Parent reference to get a days portion of its month parent.

["Monthly Revenue"]
*
([Δ"No Site", "Daily Seasonality"] / [Δ"No Site", "Daily Seasonality", "Date:Default»@Parent"])

Let me know if this helps

Thanks Ben. I do not recall seeing the Change AND @ symbols in the documentation. Can you please advise what these do. On the @ statement I assume the value of Parent will the value after all feeder cells have been calculated and if the calculation using the @, changes the parent then you get a circular reference.
Using a hierarchy sub total is not going to work as the element chosen (Project dimension) to populate from the LINK function is dynamic depending on a string input on that other cube. I want to then deduct from the LINK function the user input values on the other elements in the hierarchy so I get back to the value of the parent being equal to the LINK value. Ie users will be able to set a default Project but will be able to manually input values in other projects and the total will not change.

I can fix and make the parent equal to the link, but I still need to look for and find values on all elements in the hierarchy so they can be deducted from the element with the Link value but would prefer to have the hierarchy self add and deduct the value of all the other elements from the LINK value in the project chosen to receive the value.

3. Alternate Measure
One other option is to have a measure which populates only if it is not the element which is subject to a LINK to another cube. You can then use the parent reference (which is a relatively new feature) to return the parents value for the second measure.

This approach may work but not quite sure how to configure this How can I populate the measure ignoring the element that has the link value. I know which element of the hierarchy that has the link statement value in it.

  Here is the rule I currently have to Populate the specific Projects element 

if(ELEMENT("Projects") = Link("Budget and Forecast" ,[ "Periods:Total","Project" ]) ,LINK("Budget and Forecast",["Periods:Total", "£"]), CONTINUE)