Goal Seek function equivalent In onestream

[royari]

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.

[JackLacava]

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.

[royari]

Are there any code snippets available?

[ChrisLoran · Accepted solution]

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

[GregHertling]

hi, can this be run over multiple periods?  I need to use this functionality on a 10yr IRR calculation.

thanks,

Greg

[royari]

Yes you can put this on a finance business rule and run for multiple periods

[ChrisLoran]

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.