Window calculations provide the capability to apply specific computations across multiple rows of results. The following standard window calculation types are supported:
•Running Total – calculate a total based on the values of the current row and all previous rows
•Difference – calculate a difference based on the current row and a previous row
•Rank – assign a ranking number to the current row as compared to all other rows
•Percent of Total – calculate the percent of the sum of all rows represented by the current row
The items above can be added directly from the data item’s “Calculation” menu. But there are two more options for calculations that are only available when you select a custom calculation option:
•Moving Calculation – calculate a total based on the values of rows neighboring the current row
•Expression – a completely custom calculation built with the expression editor
Note that the available options related to window calculations depend on the type of dashboard item. A pivot table can calculate along columns or rows, for example, but a grid can only calculate along rows. The following dashboard visualizations are the only ones that support window calculations:
•Chart
•Grid
•Pies
•Cards
•Gauges
•Pivot
•Range Filter
The use of calculations imposes limitations related to data shaping features. Sorting by measure cannot be applied if the target measure has a calculation applied. Top N cannot be applied if its target measure has a calculation
To add a calculation, click the drop-down arrow that appears when you hover over a data item, and then hover over the Calculation menu item. From there, you select one of the pre-defined window calculations or you can select Custom… at the bottom of the menu.
When you select Custom… a dialog box opens to define the calculation.
After you select the calculation type, you set the window definition using the top drop-down box. This option is labeled differently for each calculation type, but it means the same thing in each case. It is the range of values that will be used for the calculation.
The following window definition examples use a pivot table because pivot tables have the most options for defining windows. Other visualizations have a subset of these window definition options.
For comparison purposes, consider the pivot table shown below. The columns are grouping by year and quarter. The rows are grouping by gender and age bracket. The individual cells show the count of contributions for each breakout, with no special calculation.
The screenshots for the following show the same pivot table with the same data, but with a running total calculation added to the count of contributions.
Direction: Columns Description: Calculates left-to-right across each row, resetting for each row
Window Definition: Each combination of gender and age bracket is one “window”; each window is a separate calculation
Direction: Rows Description: Calculates top-to-bottom down each column resetting for each column
Window Definition: Each combination of year and quarter is one “window”; each window is a separate calculation
Direction: Columns/Rows Description: Calculates left-to-right across each row from the top to the bottom row, accumulating on each row
Window Definition: The entire pivot table is one “window,” with the numbers added in left-to-right then top-to-bottom sequence.
Direction: Rows/Columns Description: Calculates top-to-bottom down each column from the far left to the far right column, accumulating on each column
Window Definition: The entire pivot table is one “window,” with the numbers added in top-to-bottom then left-to-right sequence
Direction: Columns within Groups Description: Calculates left-to-right across each row, but resets for each group of columns and for each row.
Window Definition: Each combination of gender, age bracket, and year is one “window.”
Direction: Rows within Groups Description: Calculates top-to-bottom down each column, but resets for each group of rows and for each column
Window Definition: Each combination of year, quarter, and gender is one “window.”
Direction: Columns/Rows within Groups Description: Calculates right-to-left across each row and then top-to-bottom within each group of columns.
Window Definition: Each combination of gender and year is one “window,” with the numbers adding up sequentially within the window
Direction: Rows/Columns within Groups Description: Calculates top-to-bottom down each column and then left-to-right within each group of rows
Window Definition: Each combination of gender and year is one “window,” with the numbers adding up sequentially within the window
You can also manually specify the set of dimensions that define the window. When you add a dimension to the specific window definition, you are telling the calculation to add up across all distinct values of that dimension. The dimensions you do NOT add to the window definition will establish the limits of the calculation. This is a little counter-intuitive, but with a little trial-and-error, you should be able to define any window the way you want it to work.
In the example below, all dimensions except for the calendar quarter value have been added to the specific window definition. This creates a running total down each quarter column, and the running total continues in the same quarter of the following year.
Other Calculation Options
After you select the window and direction for the calculation, you specify any other options required. Each calculation type has a different set of options.
Running Total requires a Summary function, which is “sum” by default but can be changed to any supported aggregate function.
Moving Calculation also requires a Summary function, but also calls for a Start offset and an End offset. The offset values determine how many values before and after the current cell will be brought into the calculation.
Difference lets you choose whether to calculate the difference between the current value and the Previous value, Next value, First value, or Last value. It also has a checkbox to change the calculation from a basic number to percentage of difference.
Percent of Total has no other options besides the window definition.
Rank allows for five different Rank type options: Unique, Competition, Dense, Modified, and Percentile. There is also an option to rank in Ascending or Descending order.
Expression starts with a basic sum expression, and you modify the calculation from there using the calculation functions listed below. This is the most flexible, but also the most complex calculation type because you build it from scratch. Click the Edit in Expression Editor to open a window that provides some helps for working with the expression functions.
Calculation Functions
Last() Returns the number of values from the current row to the last value in the window.
First() Returns the number of values from the current value to the first value in the window (normally a negative number).
Index() Returns the index of the current value in the window
Size() Returns the number of rows in the window
Lookup(SummaryExpression, Offset) Returns the value of the SummaryExpression at the position indicated by the Offset from the current value. Example: Lookup(Sum([Contribution Amount]), -3)
RankCompetition(SummaryExpression, Sort) Returns the competition rank for the value calculated by the SummaryExpression using the specified Sort. Example: RankCompetition(Sum([Contribution Amount], ‘asc’)
RankDense(SummaryExpression, Sort) Returns the dense rank for the value calculated by the SummaryExpression using the specified Sort. Example: RankDense(Sum([Contribution Amount], ‘asc’)
RankUnique(SummaryExpression, Sort) Returns the unique rank for the value calculated by the SummaryExpression using the specified Sort. Example: RankUnique(Sum([Contribution Amount], ‘asc’)
RankModified(SummaryExpression, Sort) Returns the modified rank for the value calculated by the SummaryExpression using the specified Sort. Example: RankModified(Sum([Contribution Amount], ‘asc’)
RankPercentile(SummaryExpression, Sort) Returns the percentile rank for the value calculated by the SummaryExpression using the specified Sort. Example: RankPercentile(Sum([Contribution Amount], ‘asc’)
RunningAvg(SummaryExpression) Returns the running average of the specified SummaryExpression from the first value in the window to the current value.
RunningCount(SummaryExpression) Returns the running count of the specified SummaryExpression from the first value in the window to the current value.
RunningMax(SummaryExpression) Returns the largest value of the specified SummaryExpression from the first value in the window to the current value.
RunningMin(SummaryExpression) Returns the smallest value of the specified SummaryExpression from the first value in the window to the current value.
RunningSum(SummaryExpression) Returns the sum of the specified SummaryExpression from the first value in the window to the current value.
WindowAvg(SummaryExpression, StartOffset, EndOffset) Returns the average value of the specified SummaryExpression using a window defined by the StartOffset and EndOffset. Example: WindowAvg(Sum([Contribution Amount]), -3, 3)
WindowCount(SummaryExpression, StartOffset, EndOffset) Returns the count of values in the window defined by the StartOffset and EndOffset. Example: WindowCount(Sum([Contribution Amount]), -3, 3)
WindowCountDistinct(SummaryExpression, StartOffset, EndOffset) Returns the count of unique values returned by the expression in the window defined by the StartOffset and EndOffset. Example: WindowCountDistinct(Sum([Contribution Amount]), -3, 3)
WindowMax(SummaryExpression, StartOffset, EndOffset) Returns the largest value in the window defined by the StartOffset and EndOffset. Example: WindowMax(Sum([Contribution Amount]), -3, 3)
WindowMin(SummaryExpression, StartOffset, EndOffset) Returns the smallest value in the window defined by the StartOffset and EndOffset. Example: WindowMin(Sum([Contribution Amount]), -3, 3)
WindowMedian(SummaryExpression, StartOffset, EndOffset) Returns the median of the values in the window defined by the StartOffset and EndOffset. Example: WindowMedian(Sum([Contribution Amount]), -3, 3)
WindowCount(SummaryExpression, StartOffset, EndOffset) Returns the count of values in the window defined by the StartOffset and EndOffset. Example: WindowCount(Sum([Contribution Amount]), -3, 3)
WindowSum(SummaryExpression, StartOffset, EndOffset) Returns the sum of values in the window defined by the StartOffset and EndOffset. Example: WindowSum(Sum([Contribution Amount]), -3, 3)
WindowVar(SummaryExpression, StartOffset, EndOffset) Returns the variance of values in the window defined by the StartOffset and EndOffset. Example: WindowVariance(Sum([Contribution Amount]), -3, 3)
WindowVarp(SummaryExpression, StartOffset, EndOffset) Returns the biased variance of values in the window defined by the StartOffset and EndOffset. Example: WindowVariance(Sum([Contribution Amount]), -3, 3)
WindowCount(SummaryExpression, StartOffset, EndOffset) Returns the count of values in the window defined by the StartOffset and EndOffset. Example: WindowCount(Sum([Contribution Amount]), -3, 3)
WindowStdDev(SummaryExpression, StartOffset, EndOffset) Returns the standard deviation of values in the window defined by the StartOffset and EndOffset. Example: WindowStdDev(Sum([Contribution Amount]), -3, 3)
WindowStdDevp(SummaryExpression, StartOffset, EndOffset) Returns the biased standard deviation of values in the window defined by the StartOffset and EndOffset. Example: WindowStdDevp(Sum([Contribution Amount]), -3, 3)
Total(SummaryExpression) Returns the total for the SummaryExpression in the current window. The total is based on the underlying data source.