4 Reports
4.10 The Grid Formula Bar
You can use formulas to calculate values for members of the account dimension. Choose the account and then
choose the icon in the formula bar ( or ) to toggle between displaying the text value in a cell and any
formula applied to the cell.
Note
This same functionality is also available in Modeler and you are recommended to maintain existing formulas in Modeler rather than in Reports. Any change made in Reports will directly affect all users of the underlying model.
To enter a formula in the selected cell start to type the text of the formula in the formula bar, the system suggests matches from the grid, which you can select to complete the formula. You can use references to other accounts for your calculation and the following arithmetic functions are also available for use with formula:
● Abs() – returns the absolute value of a number (the number without its sign).
● Average() – returns the average (arithmetic mean) of the arguments.
● Ceil() – rounds a number up to the nearest integer or to the nearest multiple of significance.
● Floor() – rounds a number down to the nearest integer or to the nearest multiple of significance.
● Max() – returns the biggest number in a set of values.
● Min() – returns the smallest number in a set of values.
● Rnd() – returns an evenly distributed random real number greater than or equal to 0 and less than 1.
● Round() – rounds a number to a specified number of digits.
● Sum() – adds all named arguments.
Note that if you divide by an account, it may have a value of zero, which can cause an error. You may want to use an advanced feature, such as conditional calculation, to prevent problems like this; open the Advanced Formula Editor dialog in which you can add more complex operators, functions, and conditions to the formula.
4.10.1 Advanced Formulas
The Advanced Formula dialog contains a number of operators, functions, and conditions that you can use to build complex formulas.
You are recommended to maintain existing formulas in Modeler rather than in Reports.
Related Information
Explore Formulas [page 25]4.10.2 Report Formulas
Simple calculations including a range of standard arithmetic functions can be used generally in Modeler and Reports. Additionally, a number of predefined formulas are available in Reports to carry out frequently required complex calculations.
This section gives details of the formulas available in Reports and gives a detailed example (based on the timevariance formula) of how the formula are entered. The following formulas are available for use in grids in report views.
Delta
Use the delta formula to calculate the difference between two sets of values for an account; for example, the difference between goods sold via online or phone channels, or the difference between actual and forecast costs. Syntax:
delta([account member 1], [account member 2])
The parameters can be dimensions, members, or versions.
Example
delta([Net_Revenue], 100)
Evaluates the result as: [Net_Revenue] - 100
delta([2014], [2015])
Calculates the absolute difference between year 2014 and year 2015.
Variance
The variance formula calculates the difference between two account members returning the result of member1 - member2.
Syntax:
variance([Member1], [Member2])
Example
variance([public.Actual],[public.Forecast])
If you enter this formula in an empty column (D) of the report next to the relevant actual and forecast columns (B and C), values will be entered in each row of D calculating the difference between the adjacent values in B and C.
Timevariance
The timevariance formula calculates the difference between the same accounts for different time periods; for example, the difference in profit for last year and the current year.
Syntax:
This formula cannot be visualized; the visualization and KPI features are not available to a view once you implement the formula.
Example
timevariance([2014])
If the second parameter is omitted, as in this example, the current year is assumed; this example (in the year 2015) is equivalent to timevariance(2014, 2015).
Year-to-date
Use YTD to calculate a running total over successive time periods; for example, calculating cumulative costs from month to month over a year. The time period starts from the beginning of the current year (either the calendar year or fiscal year) up to the present day.
Syntax:
ytd([account member])
To use the ytd formula in a grid, make sure that the Time dimension is on one of the axes, and expand it so that successive time periods are displayed. Position the cursor in the next empty column where the new data will be inserted. Enter the formula and press Enter. The system adds another column with the year-to-date values for
each row.
Example
ytd([2014])
Annualize
Use annualize to calculate a projection of a YTD value to a whole year on a monthly or quarterly basis. This formula answers the question, for example: What will my Net Revenue look like, if for the rest of the year the business performs the same way as it did from January to September?
Syntax:
Table 12:
Parameter Usage
dateTo The end date of the time frame to be projected to a whole year (to be annualized). Note that this value is a member of the time dimension and should therefore be enclosed in square brackets.
The value for this parameter can be up to 6 digits; these are interpreted as follows: – The first four digits of this value must be a valid year.
– The fifth digit represents a quarter: [20132] represents 2013, quarter 2.
– If 6 digits are present, digits 5 and 6 represent a month of the year: [201502] is February 2015, and [201411] is November 2014.
version The version the annualization is executed for.
Example
annualize([201409],[public.Actual])
Calculates an annualization based on the values of months January to September of 2014 from version Actual.
annualize([20143],[public.Forecast])
Calculates an annualization based on the values of quarters 1, 2, and 3 of 2014 from version Forecast.
Note
If you are working in a summary, you can also use the rounding formula (round). Use rounding to round off a value to a precision that you specify. The syntax is = round (value, precision), where <value> can be text or a cell reference, and <precision> indicates the number of decimal places you want the value to be rounded off to.
4.10.2.1 Working With Formula: Timevariance
In Reports, you use the timevariance formula in a grid to calculate the difference between data for the same account for different time periods.
For example, you can use timevariance to determine how last year's profit compares to this year's. The syntax for the formula is:
timevariance([Time Dimension.Member1],[Time Dimension.Member2])
To use timevariance in a grid, make sure you place the Time dimension in the first column of the grid. Then expand the Time dimension until you display the time periods that you want to compare. You must expand the dimension until these time periods are displayed because this determines what parameters you can select for the formula. For example, consider a grid in which the general and administrative expenses for 2013–2105 are displayed. To find the variance between expenses in 2013 and 2014, you must expand the Time dimension until all the years are displayed.
Next, you highlight the next empty cell in the Time dimension row. Ensure that the grid formula bar is active; the formula icon ( ) must be visible. In the formula bar, start typing the name of the formula (timevariance); the system will automatically suggest matches, and you can select timevariance from the list:
After you select timevariance, enter an opening parenthesis, and then start entering your parameters, which will be members of the Time dimension. As happened when you entered the formula name, the system will suggest matches and you select the time periods you need from the list of suggestions:
In this case, the Time dimension is expanded to the level of years, so only years are available to select as
parameters for the formula. In the parameter list, years are represented simply by the 4-digit standard format, for example 2015. Depending on the granularity of your model, you may want to calculate the variance between quarters, months, or even days. In the parameter list, these time periods are also represented in a numeric format: the first four digits always represent the relevant year, with further digits representing time periods as follows:
● 5 digits: The fifth digit represents a quarter, so [20132] represents 2013, Quarter 2.
● 6 digits: In this case, digits 5 and 6 represent a month of the year, so [201502] is 2015, February, and [201411] is 2014, November.
● 7 digits represent a week in a year.
● 8 digits represents a day in a year.
Note
If you do not choose a second parameter, the system always assumes that the second parameter will be the time period of the same granularity immediately previous to the first parameter. So if you choose 2015, for example, the system calculates the variance between 2015 and 2014.
In the example, we want to calculate the variance in expenses between the years 2013 and 2014, so those are the
years you select. The complete formula is: timevariance([2013],[2014]):
When the formula is complete, press Enter to get the results. The system adds a new column to the grid with the
calculated results. The new column is always named Variance (Time Dimension Member 1, Time Dimension
Member 2)Time Dimension Member1/Time Dimension Member 2 , so in the example, the column with the
timevariance values is called Variance(2013, 2014) 2013/2014:
To remove the formula and its results, place your cursor in any cell for which Variance is displayed in the formula bar, then delete Variance.