How To – Goal Seeking

Essbase has some great machine learning functions (e.g. @TREND), but these functions usually need tuning. This is a simple goal seeking function to help optimise a linear expression

By linear I mean a function that has 1 input, 1 output and there’s a correlation between the 2 of them).

/* Declare the temporary variables and set their initial values*/
VAR
 Target = 15000,
 AcceptableErrorPercent = .001,
 AcceptableError,
 PriorVar,
 PriorTar,
 PctNewVarChange = .10,
 CurTarDiff,
 Slope,
 Quit = 0,
 DependencyCheck,
 NxtVar;
/*Declare a temporary array variable called Rollback and base it on the Measures dimension */
ARRAY Rollback [Measures];
/* Fix on the appropriate member combinations and perform the goal-seeking calculation*/
FIX(Budget, Jan, Product, Market)
 LOOP (35, Quit)
   Sales (Rollback = Budget;
   AcceptableError = Target * (AcceptableErrorPercent);
   PriorVar = Sales;
   PriorTar = Profit;
   Sales = Sales + PctNewVarChange * Sales;);
   CALC DIM(Measures);
   Sales (DependencyCheck = PriorVar - PriorTar;
   IF(DependencyCheck <> 0) CurTarDiff = Profit - Target;
     IF(@ABS(CurTarDiff) > @ABS(AcceptableError))
       Slope = (Profit - PriorTar) / (Sales - PriorVar);
       NxtVar = Sales - (CurTarDiff / Slope);
       PctNewVarChange = (NxtVar - Sales) / Sales;
     ELSE
       Quit = 1;
     ENDIF;
   ELSE
     Budget = Rollback;
     Quit = 1;
   ENDIF;);
 ENDLOOP
 CALC DIM(Measures);
ENDFIX

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s