What is a table calculation?
Table calculation functions allow you to perform computations on values in a table.
You can use table calculations for a number of purposes - these include:
- Transforming values to rankings
- Transforming values to show running totals
- Transforming values to show percent of total
This blog post will go through how you can transform values to rankings using the INDEX, FIRST and LAST functions.
How can you transform values to rankings?
INDEX ( ) FUNCTION
The INDEX ( ) function returns the index of the current row in the partition, without sorting any field with regard to value. The first row index starts at 1 - for the first row in the partition the function will look like: INDEX() = 1.
In the example below we are using a table that shows sales by sub-category.
Example 1: Computing Using Table (down)
When INDEX ( ) is computed with the above configuration, the index of each row is 1,2,3,4,...17 (see table below).
Example 2: Computing Using Specific Dimensions
When INDEX ( ) is computed using this configuration, the index of each row - computed for each sub-category partition (Furniture, Office Supplies and Technology) - is 1,2,3,4..., etc (see table below).
FIRST ( ) FUNCTION
The FIRST ( ) function returns the number of rows from the current row to the first row in the partition. For example, when the current row index is 3, the function looks like: FIRST() = -2.
Example 1: Computing Using Table (down)
When FIRST ( ) is computed using this configuration, the offset of the first row from the second row is -1; the offset of the first row from the last row is -16 (see table below).
Example 2: Computing Using Specific Dimensions
When FIRST ( ) is computed using this configuration, the offset of the first row from the second row is -1 (etc) and this is computed for each sub-category partition (see table below).
LAST ( ) FUNCTION
The LAST ( ) function returns the number of rows from the current row to the last row in the partition. For example, when the current row index is 1 of 4, the formula looks like: LAST() = 3.
Example 1: Computing Using Table (down)
When LAST ( ) is computed using this configuration, the offset of the last row from the second row is 15 (see table below).
Example 2: Computing Using Specific Dimensions
When LAST ( ) is computed using this configuration, the offset of the last row from the second row is 2 for the 'Furniture' sub-category partition; the offset of the last row from the second row for the 'Office Supplies' partition is 7; etc (see table below).
It can be useful to compare these functions side-by-side using the same table. When computing the INDEX, FIRST and LAST functions using the Table (down) method, the values get ranked as follows:
