Skip to main content

Examples of Chart Inter Record functions

ON THIS PAGE

Examples of Chart Inter Record functions

Top Function Examples

These examples are made with the top function, but may be applied in a similar manner to the bottom, first and last functions. However, note that the first and last functions are relevant only to pivot tables.

Example 1:  

Study the straight table below, depicting the use of the top function in a one-dimensional table:

Example table image of one dimensional straight table with top function
Example table: One dimensional straight table with top function
Month sum(Val) top(sum(Val)) sum(Val) / top(sum(Val))
- 21 3 700%
1 3 3 100%
2 7 3 233%
3 11 3 367%

In the single dimension case, the top function will always refer to the first data row of the table ( The total row is not included.)

Note that expressions using the top function will be properly evaluated in the total row too since the total has a clear relation to a specific column segment, in this case the entire column.

Example 2:  

Below is a two-dimensional straight table sorted primarily on the field Grp.

Example table image of two dimensional straight table with total qualifier
Example table: Two dimensional straight table with total qualifier
Month Grp sum(Val) top(sum(Val)) top(total sum(Val))
- - 21 - 1
1 A 1 1 1
2 A 3 1 1
3 A 5 1 1
1 B 2 2 1
2 B 4 2 1
3 B 6 2 1

The top function without the total qualifier will now return the expression evaluated on the top row within the innermost sort group (Grp dimension in this case). One value will be returned for Grp = A and one for Grp = B.

By using the total qualifier in the multi dimension case, you may again refer to the absolute top row of the table with the same value being returned for all rows. The expression will of course be evaluated for the column segment spanning the entire column.

The expression using the top function without he total qualifier will evaluate to NULL in the total row, because it cannot be clearly associated with a specific column segment.

We will now convert the above straight table to a pivot table with all totals activated.

Example table image of two dimensional pivot table with total qualifier
Example table: Two dimensional pivot table with total qualifier
Month Grp sum(Val) top(sum(Val)) top(total sum(Val))
1 A 1 1 1
1 B 2 1 1
1 Total 3 1 -
2 A 3 3 1
2 B 4 3 1
2 Total 7 3 -
3 A 5 5 1
3 B 6 5 1
3 Total 11 5 -
Total - 21 - 1

The expression using the top function without he total qualifier will evaluate to NULL in the total row, because it cannot be clearly associated with a specific column segment. However, all the partial sums will be evaluated for each column segment.

The expression using the total qualifier will lack values in the partial totals but will return a value in the grand total row.

Example 3:  

Study the following straight table sorted on the field Grp:

Example table image of Two dimensional straight table sorted by Grp
Example table: Two dimensional straight table sorted by Grp
Month Grp sum(Val) top(sum(Val)) sum(Val) / top(sum(Val))
- - 21 - -
1 A 1 1 100%
2 A 3 1 300%
3 A 5 1 500%
1 B 2 2 100%
2 B 4 2 200%
3 B 6 2 300%

We may continue by changing the inter field sort order so that the chart is sorted primarily on the field Month. The table will now look like this:

Example table image of Two dimensional straight table sorted by Month
Example table: Two dimensional straight table sorted by Month
Month Grp sum(Val) top(sum(Val)) sum(Val) / top(sum(Val))
- - 21 - -
1 A 1 1 100%
1 B 2 1 200%
2 A 3 3 100%
2 B 4 3 133%
3 A 5 5 100%
3 B 6 5 120%

Above Function Examples

These examples are made with the above function, but may be applied in a similar manner to the below, before and after functions. However, note that the before and after functions are relevant only to pivot tables.

Example 4:  

Study the straight table below, depicting the use of the above function in a one-dimensional table:

Example table image of one dimensional straight table with Above function
Example table: One dimensional straight table with Above function
Month sum(Val) above(sum(Val)) sum(Val) / above(sum(Val))
- 21 - -
1 3 - -
2 7 3 233%
3 11 7 157%

The third column shows the expression sum(Val) evaluated one row above the current row, which can be confirmed by comparing with the values for sum(val) in the second column. The above function returns NULL on the first row, as there is no row above on which to evaluate the expression. The above function always returns NULL on all total rows.

The fourth column demonstrates the most typical use of this function, i.e. to calculate the difference between e.g. different time periods.

Example 5:  

Study the two-dimensional pivot table below:

Example table image of two dimension pivot table and the effect of total qualifier
Example table: Two dimensional pivot table with total qualifier
Grp Month sum(Val) above(sum(Val)) above(total sum(Val))
A 1 1 - -
A 2 3 1 1
A 3 5 3 3
A Total 9 - -
B 1 2 - 5
B 2 4 2 2
B 3 6 4 4
B Total 12 - -
Total - 21 - -

The above function without the total qualifier (third column) will only act within each sort group. A NULL value will be returned on the top row of each column segment.

When a total qualifier is added (fourth column), the entire column will be regarded as one column segment. Only the top row will return NULL. All total rows are disregarded and return NULL.

 

RowNo and NoOfRows Function Examples

This example is made with the RowNo and NoOfRows functions, but may be applied in a similar manner to the ColumnNo and NoOfColumns functions. However, note that the ColumnNo and NoOfColumns functions are relevant only to pivot tables.

Example 6:  

Study the two-dimensional pivot table below:

Example table image of RowNo and NoOfRows functions
Example table: RowNo and NoOfRows functions
Month Grp RowNo() RowNo(total) NoOfRows() NoOfRows(total)
1 A 1 1 2 6
1 B 2 2 2 6
1 Total 0 - 2 -
2 A 1 3 2 6
2 B 2 4 2 6
2 Total 0 - 2 -
3 A 1 5 2 6
3 B 2 6 2 6
3 Total 0 - 2 -
Total - - 0 - 6
  • Column 3 The RowNo function will return the row number within each sort group column segment. In subtotal rows, the row number 0 will be returned, because these totals clearly belong to a specific column segment. NULL will be returned in the grand total row.
  • Column 4 With the total qualifier, the RowNo function will return the row number within the entire column. In subtotal rows a NULL value will be returned. In the grand total row 0 will be returned.
  • Column 5 The NoOfRows function will return the number of data rows within each sort group column segment. In subtotal rows, the same number will be returned as in data rows. NULL will be returned in the grand total row.
  • Column 6 With the total qualifier, the NoOfRows function will return the number of data rows within the entire column, which is the same as what will be returned in the grand total row. In subtotal rows NULL will be returned.