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 complex 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 diving into more complex calculations it is first necessary to understand the broader context in which calculations take place in FastClose.

In the Fundamentals session we talked about members, dimensions and attributes organised into down and across axes – we need to extend that understanding a bit further.

Consider this dataset, returned from a GL Balances report:

We have three dimensions in the down axis: Department, Division and Account

And three dimensions in the across axis: Year, Period and Measures.

Each number in the grid represents an intersection of all six of these dimensions – for example the number 7,214.56 in the top row is the intersection of “Chicago”, “Corporate”, “6000 - Default Purchase Expense”, “2011”, “Actual Amount” and “Period 8”.

Whilst this single number describes the Actual consolidated Default Purchase Expenses for Chicago, Corporate in 2011, Period 8 – other numbers in the grid describe many other activities going on in Chicago, Corporate as well as other Divisions and Departments also in Period 8.

Thus, when we refer to Period 8 in a calculation, we aren’t referring to a single number, but all the numbers that intersect with Period 8 in the grid. In this report we have two columns full of numbers relating to Period 8, due to the 5 other dimensions that are also present.

Now that we see which members make up the value 7,214.56, we can use this knowledge to see the members which make up other value cells within the grid:

• On row 3, the value 578.35 is made up of members “Chicago”, “Corporate”, “6201 - Contracts-Labor Expense”, “2011”, “Period 7”, “Actual Amount”. 

• On row 8, we can see the number 7,248.90 is made up of “Chicago”, “General & Administrative”, “6480 - Dues & Publications Expense”, “2011”, “Period 9”, “Actual Amount”.

When we refer to “Period 8” in a calculation, we aren’t referring to a single number, but all the numbers that have member “Period 8” in the grid. In this report we have two columns full of numbers relating to Period 8, due to the fact that we have both “Actual Amount” and “Budget Amount” selected in the Measures filter and that Measures is pivoted across.

We could now create a simple calculation for “Quarter 3” which adds “Period 7” + “Period 8” + “Period 9”.  The FastClose calculation engine will create a new member in the Period dimension for “Quarter 3” and since the Period dimension is pivoted across, we get two new columns for “Quarter 3”, one for “Actual Amount” and one for “Budget Amount”.

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

We can see that the column numbered C7, which is the “Quarter3” “Actual Amount” column, is the sum of all the actual amount columns C1 + C3 + C5 and that C8, which is the “Quarter3” “Budget” column, is the sum of C2 + C4 + C6.

So that one simple expression Quarter3 = Period7 + Period8 + Period9 is not doing one single sum but lots of little sums which in turn cause the creation of two new columns of data, one for budget one for actuals.

It’s useful to visualise this, as when we come to do complex calculations, either cross dimensional ones, or conditional ones, it helps to understand the change from the simple case.

In a conditional calculation, we either exclude some of these individual sums from the result set relating to certain intersections of data or have those intersections override the default expression replacing it with sone other more appropriate expressions.

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

It is very common for conditional calculations to use cross dimensional expressions, these features may be combined.