Cumulative, Group, Moving and Remaining Count are supported within the Windowed Count function. This function derives a new column giving the cumulative, group, moving or remaining count of the number of rows or rows with non-null values of a value expression, when rows are sorted by a sort expression list. When one or more Partition Columns are specified, the counts are accumulated only over the rows in each partition (the start of a partition resets the accumulated count to 0). When dragging a Windowed Count function into a variable, the following tree element is created.
Sort expressions can be built up in the Sort Expressions folder, and Partition Columns can be built up in that folder. By default a windowed COUNT(*) is done, but another expression can be built up in its place, so that only rows with a non-null value of the expression are counted. The options to perform a Cumulative, Group, Moving or Remaining Count, and their associated options, are enabled through the Properties panel. Double-click on Windowed Count, or highlight it and click Properties.
-
Aggregation Style — CumulativeSecond Row Style
- None, or
- Current Row, or
- Value Preceding, or
- Value Following.
(If Value Preceding/Following)
Second Value — 0-n
- Aggregation Style — Group
-
Aggregation Style — MovingFirst Row Style
- Current Row, or
- Value Preceding, or
- Value Following.
(If Value Preceding/Following)
First Value — 0-n
Second Row Style
- None, or
- Current Row, or
- Value Preceding, or
- Value Following.
(If Value Preceding/Following)
Second Value — 0-n
-
Aggregation Style — RemainingFirst Row Style
- Current Row, or
- Value Preceding, or
- Value Following.
(If Value Preceding/Following)
First Value — 0-n