Calculated Fields, also known as Calculated Dimensions allow you to create new dimensions derived from existing data.

Unlike Calculated Members, which typically add new members within an existing dimension (eg: Qtr3 = Period 7 + Period 8 + Period 9), Calculated Fields create entirely new dimensions that can combine or transform data from multiple fields into new items.

Here is a video, or read below.

What Are Calculated Fields?

• Calculated Fields create new dimensions that can include text, dates, logical values, and numbers.

• They enable you to manipulate and combine data from different dimensions into a single, new dimension.

• For example, you can concatenate multiple segments of an account code into one field or calculate the number of days between two dates.

To access Calculated Fields, switch to the “Advanced” tab, and select “Calculated Fields”…

Once opened, you will see a list of all calculated fields defined in the report on the left side.

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 previous calculated fields.

Selecting a calculated field (in this image "CombinedSegs" shows its details on the right hand side, including its Name, Caption, and the Expression that defines it, in this case : [Segment3] & "-" & [Segment2] & "-" & [Segment1]

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. Where we have a function, it’s naming and syntax follow the equivalent function in Excel.

The area beneath the calculation expression contains buttons and controls to help build them. For example, operators to add, subtract, multiply or divide, and an & operator for concatenation. You can also type brackets if your expression needs them.

The expression builder allows you to select a dimension or attribute to add them to the expression.

Similarly functions are available, grouped by category, with a search box to help find the right one.

When you double-click a function, it is added to the expression with placeholders for parameters (which will be dimensions or attributes) you can fill in.

Examples

Example 1 : Concatenating Segment Columns

Lets say the chart of accounts has several parts or segments, representing classifications such as Region, Division and Department as well as Account or Nominal. Say you want a column which shows all these segments joined together, either for brevity or to design a FastClose hierarchy on top of.

Define a calculated dimension with an expression like (example from Epicor):

[Segment3] & "-" & [Segment2] & "-" & [Segment1]

to get a result like:

Just as in Excel, the ampersand character (&) can be used to join or concatenate text items together, there is also an equivalent Concatenate() function if preferred.

It is then possible to define a hierarchy in which particular combinations of those segments are summed under parent nodes:

which would give a result like:

(In addition, an extension to this technique to define captions for individual child account nodes – is coming in version 3.3)

Example 2: Splitting an Account Code Column

Say there is an account coding system which includes both a 'natural' account number and a sub-account number, separated by a dot. This could be the account code for 'Cash in Banks' followed by a code for the bank, for example "1000.BofA".

An expression like:

Left([Account], Find(".", [Account]) - 1)

would extract the natural account number, which could then be consolidated on.

Or to extract the bank code:

Mid([Account], Find(".", [Account]) + 1)

This assumes that the bank code follows the first dot in the account code.

Example 3: Choosing Between Alternative Columns

For those scenarios where for example, its wanted to show the Promised Ship Date if the actual ship date has not been filled in yet? Or the Customer if the Bill To Customer is blank?

In Excel you would do this by combining If() with IsBlank() and you can do the same here, though FastClose also has a dedicated function for this called Coalesce() which returns the first non-blank value in a list, for example:

Coalesce([Measures].[SalesOrderDetails].[ActualShipDate], [Measures].[SalesOrderDetails].[PromisedShipDate])

Which with the calculated dimension named Effective Ship Date would look like this:

And then the Promised Ship Date and Actual Ship Date columns could be removed, if they were no longer required.

Example 4: Days Between Two Dates

A common requirement is to report on the number of days between two dates, such as between invoice due date and today's date, for which the calculated dimension expression would look like:

Days([Measures].[AR Invoice Header].[DueDate], Today())

Though this ought to take account of whether the invoice has already been paid and perhaps be amended to:

If(IsBlank([Measures].[AR Invoice Header].[ClosedDate]), Days([Measures].[AR Invoice Header].[DueDate], Today()), 0)

Similarly, the expression for the number of days between the order date and the ship date would be:

Days([OrderHeader].[OrderDate], [Measures].[AR Invoice Header].[ShipDate])

Producing a result such as:

Limitations and Issues

Planning : Whilst it is possible to use calculated fields on reports being used for submission of planning data, they can’t be used in place of the really fundamental dimensions in planning like Period or Year.

Filtering: Reports can be filtered by calculated fields, but from a performance perspective it’s important to be aware, that the filtering takes place after data retrieval, so its advisable to filter on some other dimensions in addition, so that the report runs in a normal amount of time.

Now try the downloadable exercises below:

05.06 Calculated Fields - Exercises.pdf

Your feedback helps us do better, to fill in a short survey on this tutorial, click here.