User Tools

Site Tools


transformations:runningtotal

RUNNING TOTAL

Category: Transform / Advanced


Description

This action calculates rank (ascending and descending), cumulative count, sum, or % from the total sum for a given column inside groups, or table-wide.


Use cases

  • Calculate a running total of sales by time period (weekly, monthly, or annually).
  • Generate the order of completion in a timed event/competition (rank by time value).


Action settings

SettingDescription
New columnEnter a name for the new column.
Target columnSelect the column used to generate the selected values.
ModeSelect the mode. Options: Rank, Rank descending, Count, Cumulative sum, and Cumulative % from total. When selecting
"Rank" or "Rank descending", you will also need to select the Rank mode. See the table below for details.
GroupsSelect whether to derive the running values across all rows in the dataset, or grouped by selected columns.
Options: No groups or Reset total inside groups (and select the columns to reset the total by).


Rank modes

Rank mode Description
Dense ranking In dense ranking, values that are equal receive the same ranking number, and the next item(s) receive the ranking
number immediately following. E.g. 1,2,2,2,3.
Competition rankingIn competition ranking, values that are equal receive the same ranking number, and a gap is left in the ranking
numbers. The number of ranking numbers that are left out in this gap is one less than the number of items that
are equal. E.g. 1,2,2,2,5.


Remarks

The Rank, Rank descending and Count modes can be applied to columns with text values. Rank and Rank descending will be based on the alphabetic order of the values while Count will simply provide a cumulative count of the text values down the column.


Examples

Example: Rank rivers on each continent by length.

Source table: The longest rivers in the world

River Length(km) Continent
Nile 6650Africa
Amazon 6400South America
Yangtze 6300Asia
Mississippi 6275North America
Yenisei 5539Asia
Yellow River 5464Asia
Ob 5410Asia
Paraná 4880South America


Action parameters:

Mode is "Rank descending"
Group by Continent


Result table:

River Length(km) Continent Rank
Nile 6650Africa 1
Amazon 6400South America 1
Yangtze 6300Asia 1
Mississippi 6275North America 1
Yenisei 5539Asia 2
Yellow River 5464Asia 3
Ob 5410Asia 4
Paraná 4880South America 2


Community examples


See also

transformations/runningtotal.txt · Last modified: 2022/10/02 20:14 by craigt

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki