12-06-2022 10:17 PM - last edited on 05-02-2023 10:27 AM by JackLacava
Hi - has anyone tried to replicate excel Goal Seek function in Onestream. We have a bonus model calculation in excel which uses excel goal seek and circular reference with iterations options in Excel. We want to replicate it in Onestream. Has anyone done similar excercise.
Solved! Go to Solution.
12-09-2022 05:02 AM
Yes, but goal-seeking is definately something you don't want to put into Member Formula, or inside loops, as it is an intensive operation.
The example code below is a good example, it uses a bisection algorithm to calculate the Square Root of a number. Basically if you know fn(x) = 9, then x=3. The sqrt function is a good example where internally, it iterates and bisects various initial guesses, until it homes-in on a guess that gets to very very close to the goal.
The function MyCalculation just returns x * x, or x-squared. So it uses a binary slicing algorithm to goal-seek which input number (x) would result in the desired x-squared.
You can then implement your own formula in the MyCalculation() function, to some other calculation, and the algorithm will try to goal-seek the appropriate source amount to get the desired return amount from MyCalculation().
Imports System
Imports System.Data
Imports System.Data.Common
Imports System.IO
Imports System.Collections.Generic
Imports System.Globalization
Imports System.Linq
Imports Microsoft.VisualBasic
Imports System.Windows.Forms
Imports OneStream.Shared.Common
Imports OneStream.Shared.Wcf
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Database
Imports OneStream.Stage.Engine
Imports OneStream.Stage.Database
Imports OneStream.Finance.Engine
Imports OneStream.Finance.Database
Namespace OneStream.BusinessRule.Extender.GoalSeekExample
Public Class MainClass
Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
Try
Select Case args.FunctionType
Case Is = ExtenderFunctionType.Unknown
Dim tol As Double = 1E-6
Dim [Mid] As Double = 0
Dim goal As Double = 9
Dim iter As Integer = 0
Dim lo As Double = 0
Dim hi As Double = goal
Do While iter<100
[mid] = (lo+hi) / 2
Dim delta As Double = MyCalculation([mid]) - goal
If Math.Abs(delta) < tol Then Exit Do
If delta>0 Then hi=[mid] Else lo=[mid]
iter += 1
Loop
brapi.ErrorLog.LogMessage(si,"Found source amount =" & [mid].XFToStringForFormula)
End Select
Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
'---- This is the function that you are trying to goal-seek the input amount , from an expected result ---
Private Function MyCalculation(x As Double) As Double
Return x*x
End Function
End Class
End Namespace
12-07-2022 07:49 PM
Isn't OneStream, in the end, a giant goal-seek application... ? 😅
You can easily write rules that react to form submissions and do complicated calculations until they get acceptable results. Replicating the specific UI you have may or may not be possible, depending on specifics, but chances are that it would look and behave much better in OneStream.
12-07-2022 07:57 PM
Are there any code snippets available?
12-09-2022 05:02 AM
Yes, but goal-seeking is definately something you don't want to put into Member Formula, or inside loops, as it is an intensive operation.
The example code below is a good example, it uses a bisection algorithm to calculate the Square Root of a number. Basically if you know fn(x) = 9, then x=3. The sqrt function is a good example where internally, it iterates and bisects various initial guesses, until it homes-in on a guess that gets to very very close to the goal.
The function MyCalculation just returns x * x, or x-squared. So it uses a binary slicing algorithm to goal-seek which input number (x) would result in the desired x-squared.
You can then implement your own formula in the MyCalculation() function, to some other calculation, and the algorithm will try to goal-seek the appropriate source amount to get the desired return amount from MyCalculation().
Imports System
Imports System.Data
Imports System.Data.Common
Imports System.IO
Imports System.Collections.Generic
Imports System.Globalization
Imports System.Linq
Imports Microsoft.VisualBasic
Imports System.Windows.Forms
Imports OneStream.Shared.Common
Imports OneStream.Shared.Wcf
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Database
Imports OneStream.Stage.Engine
Imports OneStream.Stage.Database
Imports OneStream.Finance.Engine
Imports OneStream.Finance.Database
Namespace OneStream.BusinessRule.Extender.GoalSeekExample
Public Class MainClass
Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
Try
Select Case args.FunctionType
Case Is = ExtenderFunctionType.Unknown
Dim tol As Double = 1E-6
Dim [Mid] As Double = 0
Dim goal As Double = 9
Dim iter As Integer = 0
Dim lo As Double = 0
Dim hi As Double = goal
Do While iter<100
[mid] = (lo+hi) / 2
Dim delta As Double = MyCalculation([mid]) - goal
If Math.Abs(delta) < tol Then Exit Do
If delta>0 Then hi=[mid] Else lo=[mid]
iter += 1
Loop
brapi.ErrorLog.LogMessage(si,"Found source amount =" & [mid].XFToStringForFormula)
End Select
Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
'---- This is the function that you are trying to goal-seek the input amount , from an expected result ---
Private Function MyCalculation(x As Double) As Double
Return x*x
End Function
End Class
End Namespace
01-24-2023 09:27 AM
hi, can this be run over multiple periods? I need to use this functionality on a 10yr IRR calculation.
thanks,
Greg
01-24-2023 09:29 AM
Yes you can put this on a finance business rule and run for multiple periods
01-24-2023 10:04 AM - edited 01-24-2023 10:15 AM
As royari says, first thing I would do is put this into a Finance BR, but I would add this should be specifically on the Custom Finance Calculate , not standard cube calculate : You don't want this sort of expensive calculation to get run again and again for every time period/every entity, every time you run a consolidation.
Then test it for one period : Launch the Custom Finance BR from a simple dashboard button, or from a Data Mgmt job, so it runs a Custom Finance BR (not standard calculate) , and then enter a Time Specification so it runs for just one time period.
Then go back to the Data Mgmt step, and update the Time Specification to an XFBR rule that returns 120 time periods, starting from the current POV Time or WF time.
The XFBR would need to generate a string like this:
T#2022.Months, T#2023.Months, .... etc... , T#2031.Months
That would re-execute the Custom Finance Rule for each of the 120 time periods.
If you want to do this for multiple entities, then put that in the Entity filter. But that's going to be a lot of data units being calculated , < Num Entities> * <Num Periods> so will likely take a while to calculate.
For long range plans/forecasts, I would consider if yearly data frequency is sufficient for anything over 2-3 years in advance. Don't forget that in OneStream you can have a scenario that has varying time frequency by year, so next year or two maybe monthly data frequency, then more future years are only annual. This would enormously improve processing time vs calculating 120 periods * <num entities>.
Finally don't forget when working with Custom Finance Rules in this way, to set cell values using the Durable option, and explicitly clear previously calculated cells. Otherwise your calculated amounts will be wiped next time you run a consolidate.