transformations:runningtotal
Table of Contents
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
| Setting | Description |
|---|---|
| New column | Enter a name for the new column. |
| Target column | Select the column used to generate the selected values. |
| Mode | Select 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. |
| Groups | Select 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 ranking | In 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 #1
Create a running total through all salespeople.
Before (source table)
| Salesperson | Sales |
|---|---|
| Sveta | 11230 |
| Rick | 7650 |
| Dan | 8290 |
| Yelena | 9200 |
| Mary | 10340 |
After (result table)
| Salesperson | Sales | Running total |
|---|---|---|
| Sveta | 11230 | 11230 |
| Rick | 7650 | 18880 |
| Dan | 8290 | 27170 |
| Yelena | 9200 | 36370 |
| Mary | 10340 | 46710 |
Action parameters
New column: Running total
Target column: Sales
Mode: Cumulative sum
Groups: No groups
Example #2
Rank rivers on each continent by length.
Before (source table)
| River | Length(km) | Continent |
|---|---|---|
| Nile | 6650 | Africa |
| Amazon | 6400 | South America |
| Paraná | 4880 | South America |
| Ob | 5410 | Asia |
| Yangtze | 6300 | Asia |
After (result table)
| River | Length(km) | Continent | Rank |
|---|---|---|---|
| Nile | 6650 | Africa | 1 |
| Amazon | 6400 | South America | 1 |
| Paraná | 4880 | South America | 2 |
| Ob | 5410 | Asia | 2 |
| Yangtze | 6300 | Asia | 1 |
Action parameters
New column: Rank
Target column: Length (km)
Mode: Rank descending
Ranking: Dense
Groups: Reset total inside groups
Group columns: Continent
Community examples
- A way to obtain all positions of a char in text (Project; Module: Main; Group: Tab 1; Table: Table 2; Action position: 5)
- Converting Text to HTML (Project; Module: Main; Group: Tab 1; Table: Build HTML (alternative); Action position: 6)
- Iterate same row in table a known number of times (Project; Module: Main; Group: Group 1; Table: Table 1; Action position: 4)
See also
transformations/runningtotal.txt · Last modified: 2025/01/12 23:35 by craigt