03.01 Simple Calculations
Overview
FastClose includes a powerful calculation engine capable of creating and adding new information to embellish your report. Because it is so flexible, we are not going to tackle the whole system in one piece but break it down and tackle in stages at the right time as the user’s understanding of FastClose grows.
Here, we are going to start by tackling the simplest type of calculation: Single Dimension Calculations.
Here is a video, or read below.
Simple Calculations
The simplest sorts of calculations are of the form A = B + C
In FastClose this type of calculation is common and many templates ship with them predefined.
For example Qtr1 = Period 1 + Period 2 + Period 3
This particular calculation ships as standard in the “GL Balances” template. Let’s take a look at how this appears in the FastClose User Interface.
Switch to the “Insert” tab, and select “Calculations”…
…which will display the Calculation builder dialog:
On the left we have a list of all the calculations in the report. These can be grouped into convenient sets using the arrows to the left. Or copied and pasted using the buttons at the top to create variations on calculations.
To the right we have the details of the currently selected calc, in this case our Qtr1 calc.
We can see it has a name “Qtr1” which is what we would use to refer to it if we were to select it in a filter or use it as part of another calculation.
It also has a caption “Quarter 1” which is how it is displayed when it appears in the grid.
And finally the expression that defines the calc, in this case :
[Period].[1]+[Period].[2]+[Period].[3]
You can type the expression directly into this area if you are familiar with the syntax, or you can build it up by clicking on items in the area beneath.
The area beneath the calculation expression contains buttons and controls to help build them. For example, operators to add, subtract, multiply or divide, and to specify ranges. You can also type brackets if your expression needs them.
There are also controls to work with subtotals, create conditional calculations and more, that will be covered in later guides.
The area is dominated by the member picker. This allows you to select a dimension, in the screenshot this is “Fiscal Period”, so that you can then double click on the members of that dimension, to add them to the expression.
Simple Syntax
When you double click a member in this area it adds it to the expression. It is described textually using the syntax [Dimension].[Member]
Each term must conform to this notation. If there is an error in the expression, the expression box will highlight in red with explanatory hover text, so that you know there is an issue.
Other calculations in the Balances template include:
Variance which looks like this
[Measures].[Balance]-[Measures].[Budget]VariancePct which looks like this
[Measures].[Variance]/[Measures].[Budget]
So Variance is calculated by taking the Balance member from the measures dimension and subtracting the Budget member from the measures dimension.
VariancePct is calculated by taking the Variance number we have just calculated, an example of basing one calc on another, and dividing it by the Budget member from the measures dimension. There is no need to multiply by 100 if you apply a percent format to the resulting data in the formatting area.
In a simple calc, every term must come from the same dimension.
Ranges and Wildcards
Ranges and Wildcards can be used as an efficient way to add long lists of members together.
For example if you wanted to add a range of account codes together you could use the range operator “:” to capture them.
So [Account].[1000] + [Account].[1001] + [Account].[1002] + [Account].[1003] + … + [Account].[1999] can instead be written [Account].[1000]:[Account].[1999]
This also has the advantage that should new codes become active in the account range, reports will pick them up automatically.
The wildcard operator “*” can be used to pick up members by pattern matching. [Company].[UK*] could be used to add up all the Company codes that start with UK to provide a total of all subsidiaries operating in that country.
This is best suited to text based members rather than numeric ones. If used on account codes for example, 10* would pick up all codes starting 10* including 10, 100, 1023, 100456 etc… However these codes may represent items of different granularity and already be included in each other.
Exercises and Tutorial Survey
03 Simple Calculations - Exercises.pdf
Your feedback helps us do better, to fill in a short survey on this tutorial, click here.