05.03 Cross Dimensional Expressions

Cross Dimensional Calculations are probably the most challenging calculation concept to understand in FastClose but along with conditionals make FastClose calculations very powerful.

Let’s take a simple calc in the measures dimension, an expression for a Forecast such as:

Actual * 1.2        ie: My forecast is an uplift of 20% on my actuals

A few of the individual calculations are circled in red to highlight what is going on.

We can understand better what is happening if we expand this expression

[Measures].[Forecast] = [Measures].[Actual] * 1.2

to include all the dimensions in each cell intersection in the report, this gives us:

[Measures].[Forecast][Year].[*][Period].[*][Department].[*][Division].[*] = [Measures].[Actual][Year].[*][Period].[*][Department].[*][Division].[*] * 1.2

This show all dimensions on both sides of the expression. This is to highlight how the result of an individual sum goes to a related cell in the report results.

Where no selection is made in a dimension (shown as [Dimension].[*]) above, the absence of any criteria is implied and the term is omitted. By not specifying any criteria for a dimension it in effect means that all members of the dimension will be selected. Hence, by specifying only [Measures].[Actual] in the calculation, we are in effect saying that we want this expression to apply to all Years, all Periods, all Departments all Divisions and all Accounts as returned by the report.

Of course in practice only the expression after the = sign goes into the expression box in the calculation screen as shown below:

In a cross dimensional calc however, we start to explicitly state members from these other dimensions. So if we wanted to revise this forecast to base it on an uplift of 20% on the Chicago numbers instead, you would get:

[Measures].[Forecast][Year].[*][Period].[*][Department].[*][Division].[*] = [Measures].[Actual][Year].[*][Period].[*][Department].[Chicago][Division].[*] * 1.2

Which we would actually write as: [Measures].[Actual][Department].[Chicago] * 1.2

This is a cross dimensional calculation, because a single term in the expression includes criteria from more than one dimension, in this case Measures and Department. There is no limit to the number of dimensions that can be crossed in a term.

It is important to note that this is not the same as a conditional calculation, this expression will still fire for every department returned by the dataset but the data for any other department will be based, in this case here, on an uplift of the Chicago data, not its own data.

Taking this further, as this is a forecast, we would also want to base the uplift on last year’s numbers rather than this year’s, hence it would become:

[Measures].[Actual][Year].[2020][Period].[*][Department].[Chicago][Division].[*] * 1.2

Which we would write as:

[Measures].[Actual][Year].[2020][Department].[Chicago] * 1.2

In the calculation dialog this would look like this:

Where [Measures].[Balance] are the Actuals and [Segment2].[00] is Chicago.

NB: The first term in the expression is always used to determine the dimension the calc is to be placed in. If the first term is cross dimensional as here, the first dimension mentioned in the term is used, which is why we wrote this term as [Measures].[Actual][Department].[Chicago] and not as [Department].[Chicago] [Measures].[Actual]  Ordering the dimensions the second way would have placed the new forecast member in the Department dimension, which we don’t want. The order of dimensions in subsequent cross dimensional terms does not matter.

It is also possible to use ranges within cross dimensional terms. For example if we wanted to amend the expression such that it only considers the sum of a certain account range, then we would specify the account range in the calculation.  For example:

[Measures].[Actual][Year].[2016][Department].[Chicago][Account].[4000][Account].[4999] * 1.2

This calculation will still fire for all Years, all Periods, all Departments and all Divisions but now we are we are basing the uplift on the sum of just our sales figures in 2016 for Chicago.

Exercises and Tutorial Survey

05 Advanced Calculations - Cross Dimensional Expressions - Exercises.pdf

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