Need help updating member formula calculation for base level accounts

Question

Hi Folks,I need help updating a UD5 member formula that should impact the base level accounts only. I want the consolidation to aggregate/consolidate parent accounts with the calculated base level values.Currently my logic calculates all accounts including the base and parents and no consolidation/aggregation takes place. Basically i want the calculation to happen like the logic below:For example:For a given UD5 Member i.e. growth the member formula is driving certain calculations for the account hierarchy members.  For a parent account 1200, i want the member formula to update values for all its base members using the logic 2*3.For all other accounts beside the account 1200 in the account hierarchy, i want the following logic applied 2+3 at the base level. After these two set of calculations at base level accounts, i want natural OS aggregation to calculate the rest of the accounts in relation to ud5 member where the logic is being applied.Does anyone know how to go about this? Currently i am stuck at applying the logic to base level members only for both if and else if conditions. Thanks in advance.-Uzimon

sameburn · Accepted Answer

Here is a very simple example of one way (there are many other ways) to do this using the api.Data.Calculate (ADC) method in a Finance rule and passing in your account filter as a member expansion and setting the amount for each set of base account members.

' Declare or retrieve the amount that you want to set as a Decimal						
Dim amt As Decimal = Decimal.Zero
' Use ADC (api.Data.Calculate) formula with Account filter on your first parent account base members and set the Amount e.g. 0.00
api.Data.Calculate($"Cb#YourCube:E#YourEntity:S#YourScenario:T#YourTime:V#YourView = {amt}","A#YourParentAccount1.Base",,,,,,,,,,,,,, True)
' Set the amount as something else
amt = 2*3
' Use ADC formula with Account filter on your second parent account base members and set the Amount
api.Data.Calculate($"Cb#YourCube:E#YourEntity:S#YourScenario:T#YourTime:V#YourView = {amt}","A#YourParentAccount2.Base",,,,,,,,,,,,,, True)
' Repeat as required

There is also enablement on how to do this via Navigator e.g. https://onestream.thoughtindustries.com/learn/course/writing-financial-calculations

There is also information in the Design & Reference guide e.g. Finance Business Rules and Finance Functions APIs

Hope this helps

Sam

sameburn · Answer

Hi ​@usmaliI would recommend reaching out to Remote Consulting via Support if you still need assistance with thisYour use case is very specific to your Data Model; so I am not sure you are going to get the help you need here e.g. without somebody also having access to your Data ModelPlease be aware that this will likely be considered a billable activityHope this helpsSam

Sign up

Login with SSO

Login to the OneStream Community

Login with SSO