Tell us what you think

We want to create the best possible experience for our learners. Your feedback helps us to improve the experience for everyone.

Redefining Calculation Contexts

Objective

After completing this lesson, you will be able to force input and output calculation contexts

Input and Output Contexts

The calculation context is the data that a calculation takes into account to generate a result. This means that the value given by a measure is determined by the dimensions used to calculate the measure.

Most reports contain two kinds of objects:

Dimensions
Dimensions represent business data that generate figures. Store outlets, years, or regions are examples of dimension data. For example, a store outlet, a year, or a region can generate revenue. We can talk about revenue by store, revenue by year or revenue by region.
Measures
Measures are numerical data generated by dimension data. Examples of measures are revenue and number of sales. For example, we can talk about the number of sales made in a particular store. Measures can also be generated by combinations of dimension data. For example, we can talk about the revenue generated by a particular store in 2012.

Input and Output Contexts

A calculation has an input context and an output context. You can specify neither, either, or both explicitly.

The input context is the set of dimensions used to make the calculation.
The output context is a set of dimensions that functions like a break in the calculation.
It causes the calculation to output values as if they are calculated in the footer of a table break.

Determining the set of dimensions used to generate a measure is called defining the calculation context (or forcing the calculation context).

Input Calculation Context: Forcing

Suppose that you have a report showing the maximum revenue for each State and Year, but does not include the Year in the block.

When specified explicitly in a formula, these input contexts are:

=Max([Sales revenue] In ([Year]))
=Max([Sales revenue] In ([Year] ; [State]))
The dimensions in the input context appear inside the parentheses of the function (in this case, Max), whose input context is specified.

Force the Input Calculation Context

ExerciseStart Exercise

Output Calculation Context: Forcing

The output context causes the formula to output a value as if it is placed in the footer of a block containing a break.

The report shown in the figure, Specifying an Output Context, shows sales revenue by year and quarter, with a break on year, and the minimum revenue calculated by year.

What if you want to show the minimum revenue by year in a block with no break? You can do this by specifying the output context in a formula. In this case, the formula is as follows:

=Min([Revenue]) In ([Year])

That is, the output context appears after the parentheses of the function whose output context you are specifying. In this case, the output context calculates the minimum revenue by year. If you add an additional column containing this formula to the block, the result is as shown in the figure Minimum Sales Revenue by Year.

Output Context

You can see that the Min by year column contains the minimum revenues that appear in the break footers in the previous report.

Notice that in this example, the input context is not specified because it is the default context (Year, Quarter) for the block. In other words, the output context determines which revenue by year and quarter to output. In full, with both input and output formulas explicitly specified, the formula is as follows:

=Min([Sales Revenue] In ([Year] ; [Quarter])) In ([Year])

Explained in words, this formula calculates revenues by year by quarter, then outputs the smallest of these revenues that occurs in each year.

What would happen if you did not specify the output context in the Min by year column? In this case, these figures would be identical to the figures in the Sales Revenue column. Why? Remember that the default context in a block includes the dimensions in that block. The minimum revenue by year by quarter is the same as the revenue by year by quarter simply because there is only one revenue for each year/quarter combination.

Force the Output Calculation Context

ExerciseStart Exercise

Next lesson