Overview

FastClose includes a powerful calculation engine capable of creating and adding new information to embellish your report. We tackled the simple calculations of the sort A = B + C in the Simple Calculations session – now we are going to tackle the more advanced stuff.

In the following session we are going to cover:

• Conditional Calculations

• Cross Dimensional Expressions

• Simple calcs based on attributes

• Calculation Priority

• Two Pass Calculations

• Calculations based on Variables

• Calculations based on Buckets

Background

Before looking at more complex calculation concepts it is helpful to first look at the context in which calculations take place .

Each number in the grid relates to one item in each and every dimension

Here is an example dataset, returned from a GL Balances report:

In the Fundamentals session we talked about the positioning of dimensions either in the across axis or down axis of the report.

In this report then, the first three columns cover three dimensions that have been placed in the down axis (Department, Division and Account) whilst the remaining columns contain the across axis, containing the Year, Period and Measures dimensions.

So each number in the grid then represents an intersection of one item from each of these six dimensions – for example the number 7,214.56 in the top row represents the Actual consolidated Default Purchase Expenses for the department Chicago, in division Corporate in 2011, Period 8 (you should be able to count 6 items in bold there).

Other numbers that we then see on this grid describe other activities going on in Chicago, Corporate in 2011 in either Period 7 or 8 such as contracts or freight expenses.

So when we refer to Period 8 in a calculation in this report, we aren’t referring to a single number, but all the numbers that are in the Period 8 columns. Here there are two, both for 2011, one for the “Actual Amount” and the other for the “Budget Amount”.

A single FastClose calculation expression is the same as lots of little Excel calculations

Now, in Excel, if we wanted to create a new pair of columns to display Quarter 3 as shown below. We would have to create a formula in each and every one of those yellow cells to add the source cells together to create the answers. Lots of little calculations, one per target cell.

We have highlighted two of these sums, one in blue and one in red to help visualise what would be going on in Excel.

We can see that the cell numbered J4 in the “Quarter3” “Actual Amount” column, is the sum of all the actual amount columns D4 + F4 + H4 and that K5 in the “Quarter3” “Budget” column, is the sum of E5 + G5 + I5. And we would have similar formulae for all the other cells, complete with all the cut & paste maintenance errors that would creep into those over time.

By contrast, in FastClose, we create one single calculation formula and FastClose uses the shape of the report to determine which cells that expression will fill and where to pick up the source data. So instead of lots of cell formulae (with all the potential for cut & paste maintenance errors) we have just one reliable expression.

So we would create a single calculation for “Quarter 3” adding “Period 7” + “Period 8” + “Period 9” and the calculation engine would create a new member in the Period dimension for “Quarter 3”. Since the Period dimension is in the across axis, we would get both of the two new columns we require for “Quarter 3” automatically, one for “Actual Amount” and one for “Budget Amount”, as shown above in yellow.

Coming up Next

In a conditional calculation, we either exclude some of these individual cell level sums from the results or replace the default expression for certain cells with something more specific.

In a cross dimensional expression, we would start to amend these individual sums, whose terms appear to be laid out in a regular manner in the diagram, to expressions that reference cells in other locations, in effect moving individual terms circled to other cells.

It is very common for conditional calculations to make use of cross dimensional expressions.