Rules for Calculations
You add a calculation to an existing tier to calculate on that tier. For example, add a custom calculation to a levels tier to calculate on levels. See Add Calculations to a Matrix Report. A calculation formula can reference the following elements in a report:
- The elements in the Elements panel that are independent of the report properties or the user session. These elements are not on the report rows, columns, filters, or sheet tabs. For example, if you are calculating on accounts, you can use the same accounts in a formula as the ones that show under Elements > Accounts.
- The elements in the canvas such as on the rows, columns, filters, or sheet tabs. These elements are dependent on some report property or the user session. A calculation refers to an element in the canvas using the element code. For example, Current Version is an element on the canvas that does not refer to a specific value of the Version dimension. It changes to match whatever version the current user is on when running the report.
Formula Syntax Examples
Enclose these elements in square brackets using
[account code] or
- Accounts. For example, to calculate gross margin:
- Custom dimensions. For example, to total the US, Japan, and Canada dimensions:
- Level dimensions. Use the plus
(-)enclosed in parentheses to roll up parent elements. For example:
[Operations(+)]refers to the rollup of operations.
[Operations(-)]refers to the "operations only" level
LevelRollup(). Use this function to roll up account data on specified levels. Data rolls up based on how the level rollup is set for Accounts in Model Management. For example, to roll up the data for the Marketing, Engineering, and Sales levels on a single tier, use the syntax:
LevelRollup([Marketing], [Engineering], [Sales]). If a level rollup contains a level that is a subset of another level, the parent is used and the sub-level is ignored. This function is only available in custom calculation elements in reports.
RPT syntax to refer to elements in the report canvas using their codes. For example:
- To refer to the first month in the report, use
- To calculate a region's share of the total, use
DIV(RPT.SelectRegion,[Region(+)])*100.In this example,
Select.Regionis the code applied to a specific level such as Sales - North.
Region(+)represents the parent level for that specific level such as Operations.
Element is a report parameter
If an element is a report parameter, the parameter value reflects in the calculation. For example, a report displays time (Jan, Feb, March) on the columns and time is a parameter. A Subtotal column adds the values for the months. When you change the parameter to display Feb, March, and April, the Subtotal column now adds the values for these three months. To sum the three periods shown in the report, use:
RPT.FirstMonth + RPT.SecondMonth + RPT.ThirdMonth.
ThirdMonth are the codes for the time elements.