MovingCount

Returns the moving count of a list of values. The calculation can be restarted based on attributes set in the function parameters. This is an OLAP function.

Syntax

MovingCount <BreakBy={}, SortBy=()> (Argument, WindowSize)

Where:

• BreakBy is the attribute indicating where the calculation restarts.
• SortBy is the attribute or metric by which the data is sorted.
• Argument is a metric representing a list of values.
• WindowSize is a positive integer indicating the highest number to use in the count.

Expression

Where:

• yi= metric value at the ith row
• m= window size
• n = number of rows/metric values
• 1i = 1 if the value at the ith row is not NULL
• 1i = 0 otherwise

Usage Notes

If there are more entries in a section than the integer in the window size parameter, then all the remaining entries are given the highest number in the count. For example, if the window size is 4 and there are 6 entries in the list section, they are counted as follows: 1,2,3,4,4,4.

Example

This simple example demonstrates how the MovingCount function counts rows of data. This report uses the attributes Region and Employee, and the metrics Revenue and Moving Count. A filter is applied so the only Regions displayed are South, Northwest, and Southwest. The Moving Count metric is defined as follows:

MovingCount<BreakBy={Region}, SortBy= (Value) >(Revenue, 3.0)

• The count restarts for every Region.
• The entries are counted based on the value of the metric Revenue in ascending order (the lowest value is counted as 1, next lowest is 2, and so on).
• The highest number in the count is 3 as designated in the WindowSize parameter.