Time-Based Filtering in SAC Stories: Using Model Variables for Measure Restriction

[Sebastian_Gesiarz]

Consider a scenario where planning data needs to be compared to actuals, and the actuals include adjustments (i.e., Actuals = Actuals + Adjustments). Depending on the input control selection, it may be necessary to exclude certain periods from these adjustments. This can be achieved in a few simple steps:

Create a Model Variable
First, create a model variable with the appropriate data type to handle the time-based offset.

Use the Variable in a Restricted Measure
Use this variable as an offset in the restricted measure for adjustments:

IF([d/Date].[p/FISCAL_CALPERIOD]>=['ActualsOffsetStartPeriod'],[AdjustmentsMeasure] ,NULL )​​

Initialize the Variable on the Screen Load
Set an initial value for the model variable when the screen loads, preferably in the landing page so the table does not refresh multiple times:

Application.getFileDataSource("ModelId").setVariableValue("ActualsOffsetPeriod", "Sample Fiscal Period");​​

Update the Variable Based on Input Control Selection
Determine which planning scenario was selected in the input control, execute custom logic to obtain the correct Fiscal Period for the offset, and update the model variable accordingly:

var strSelectedScenario = icSampleInputControlSelection.getInputControlDataSource().getActiveSelectedMembers()[0].id;
var strChosenFiscPeriod = yourLibrary.getSampleFiscPeriod(strSelectedScenario);

Application.getFileDataSource("ModelId").setVariableValue("ActualsOffsetStartPeriod", strChosenFiscPeriod );​

By following these steps, it is possible to dynamically restrict a measure in an SAC Model using a variable value passed from a custom script.