For example, you can use the SELECT function to compare values between different versions.
Source[SELECT: Target item]
|Source||Number, Boolean, date, time period, list, or text|
The source module and line item to select a value from.
This should follow the syntax below:
|Target item||Time period, list|
The item for the SELECT function to return the value from.
This should follow one of the syntaxes below:
The SELECT function returns a value of the same data type as the Source argument.
Income Statement.Sales[SELECT: Versions.Actual]
In this example, there's a source and a target module.
The structure of the Products list within General lists is as below:
The Sales module source module below has the Product list and line items on pages. The Gross sales line item and Nuts product display. The Time and Organization lists are on columns and rows respectively.
|Jan 22||Feb 22||Mar 22|
The target module has the Organization list on pages and London displays. The Time list is on columns, and line items on rows. Each line item selects the value for each hardware item of the Product list, including the aggregated total. You can see the effect of the SELECT function in the Nuts line item, which selects data from the example source view above.
|Jan 22||Feb 22||Mar 22|
Time period example
There are two modules in this example, which uses the SELECT function to select values for certain time periods.
The source module, Product sales, has the Time dimension on columns and the Organization list on rows. Line items and the Product list are on pages, and the Gross sales line item and Hardware list item display.
|Oct 21||Nov 21||Dec 21||Jan 22||Feb 22||Mar 22|
The target module, Recent sales, has the Organization list on columns and line items on rows. The Product list is on pages and the Hardware list item displays.
The current period in the Model Calendar is Mar 22, the Year To Date Summary is enabled. The start period of the calendar is Oct 21.
Sales over all periods
Sales this year to date
Sales in the current period
This example demonstrates how to use SELECT to compare two versions, Forecast and Actual, without having to add an extra version. Adding extra versions increases the size of the model.
There are two modules in this example. The source module, Income statement, and the target module, Income statement variance.
The source module, Income statement module has the Organization list on rows. The Versions dimension is on columns. Line Items and the Time dimension are on pages. The Sales line item and Apr 22 time period display.
You want to pull the data for the Sales line item for the Actual and Forecast versions into the target module.
The target module, Income statement variance, has Line Items on columns, and the Organization list on rows. The Time dimension is on Pages and the Apr 22 period displays.
You can see that the values in the Actual sales and Forecast sales line items are selected from the Actual and Forecast versions in the source module. The Variance values come from the simple formula
Actual sales - Forecast sales.