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
Rollup(). Use this function to aggregate data based on the rollup mode (for example, sum or average) that is set for the account in Modeling. For example, to roll up the data for the Marketing, Engineering, and Sales levels, use:
Rollup([Marketing], [Engineering], [Sales]). If a rollup contains a value that is a subset of another value, the function uses the parent value and ignores the subset. This Rollup() function is only available for 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:
Select.Regionis the code for a specific level such as Sales - North.
Region(+)represents the parent level for that specific level such as Operations.
For example, you want to show what percentage of the total sales revenue the North America (NA) region contributes. To create this report, you add the Sales Revenue account and NA and its parent region to your report rows. You drag a custom calculation into the regions tier. On the columns, you add time and level (HQ). Add a formula to the custom calculation to calculate the NA share of sales revenue as a percentage of the region total.
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.