User Tools

Site Tools


transformations:runningtotal

Running total

This transformation calculates rank, cumulative count, sum or % from total sum for a given column inside groups, or table-wide.

EXAMPLE

Source table: The longest rivers in the world

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

Objective: Rank rivers on each continent by length.

Transformation parameters:

  • Mode: Rank descending
  • Group by: Continent

Output table:

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

Ranking modes

Note that ranking can be done in one of two modes that return different results in cases when some column values occur more than once:

Dense ranking - In dense ranking, items that compare equally receive the same ranking number, and the next item(s) receive the immediately following ranking number. E.g. 1,2,2,2,3.

Competition ranking - In competition ranking, items that compare equal receive the same ranking number, and then 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 compared equal. E.g. 1,2,2,2,5.

The ranking mode can be specified in the action settings.

transformations/runningtotal.txt · Last modified: 2019/04/16 17:32 by dmitry