Redefining Calculation Contexts
After completing this lesson, you will be able to:
- Force input and output calculation contexts
Input and Output Contexts
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
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
