As well as editing and changing the properties of a context Calculation, such as its name or format, it is also possible to alter the logic of how the calculation is performed. This allows the behavior of how the calculation is evaluated to be changed depending on your requirements. For example, if a cumulative context calculation has been created, it is possible to set breakpoints on the cumulative totals, resetting them to the beginning when a certain window of results closes. For this reason, this type of logic control is often referred to as "Window Functions".
Use Case Example
Consider this View, where the Sales Measure is calculated as a Context Calculation cumulative total through Years and Quarters:
The cumulative total runs from the top of the Cumulative Sales column through to the bottom, resulting in a final value which is the total of all the quarters across all years.
However, it is a common requirement to reset the cumulative totals at the beginning of each new value of Date year. Adjusting the Context Calculation Logic allows the cumulative total to be reset in this way:
The cumulative totals reset back to zero on the boundary between one year and the next. In effect, the cumulative totals in this instance are operating within the "window" defined by the outermost Hierarchy values in the View, or the values of the first Hierarchy defined in the Rows Drop Zone, in this case, year.
Editing Context Calculation Logic
The Context Calculation Logic Editor is accessed through the Report Menu ribbon:
Or through the right click context menu on the Context Calculation chip in the Drop Zone or from the Custom Calculation section of the Measures panel::
Any of these options will bring up the Context Calculation Logic editing panel.
Understanding the Context Calculation Logic panel
The Context Calculation Logic panel will differ in its content depending upon the level of complexity of the logic to be applied. The differing views are determined by the Compute Used drop down. there are three modes available, Auto, Drop Zone and Specific Chip.
Auto Mode
In Auto mode, the default logic of Context Calculations are preserved, defined by the layout and context of the query.
- Click here to learn more about how Context Calculations behave in their default form.
Drop Zone Mode
In Drop Zone mode, two additional panels appear, Drop Zones and Extra Details.
Drop Zones
The window over which the window over which the Context Calculations derived is defined can be rows and / or columns. The check box next to each can be selected and the option in the Extra Details panel will be applied to one or the other or both, depending on what has been selected.
Extra Details
Under Extra Details the options from the drop down consist of:
- All: the Context Calculation will be evaluated over the entire result set. Equivalent to the default.
- Deepest: the Context Calculation will be evaluated over the window of the lowest of the nested hierarchies.
- Custom Selection: the Context Calculation will be evaluated over the window of the specified hierarchies.
All Example
The cumulative sales runs form top to bottom, equivalent to the default or Auto Mode
Deepest Example
With Deepest option selected, the cumulative sales resets for every change in value of the next hierarchy up in the nested hierarchies, in this case manufacturers.
Custom Selection Example
With Custom Selection option selected, individual hierarchies can be selected to define the window over which the Context Calculations are evaluated. In this case, Manufacturer and Product Category are selected, defining the window of cumulative calculation to be manufacturer, resetting the cumulative value to zero on each change of Manufacturer value.
Note that the chips in the Extra Details panel can be reordered. Swapping the order of the Manufacturer and Product Category chips will still evaluate over the window of Manufacturer, but each Product Category is accumulated individually:
Specific Chip Mode
In Specific Chip mode one additional panel appears, allowing you to specify which chips to select to define the window over which the Context Calculations will be evaluated irrespective of the Rows and Columns. This provides the greatest degree of flexibility and a very high degree of sophistication in how the Context Calculations are evaluated.
As well as selecting which chips play a part in defining the evaluation window, the order can be changed using the grab handles to the right of the selected chip. This again will redefine how the values are calculated. With three chips in play, there are a total of 15 different combinations of selection and ordering possible.
Multiple Context Calculations
It is also possible through the Context Calculations Editor to define separate modes of operation for each Context Calculation where there are two or more being used. In this example cumulative sales is being evaluated over the Manufacturer window, while the standard deviation sales is evaluated over the Product Category window.'
