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