Above() evaluates an expression at a row above the current row within a column segment in a table. The row for which it is calculated depends on the value of offset, if present, the default being the row directly above. For charts other than tables,
Above([TOTAL] expr [ , offset [,count]])
Return data type: dual
||The expression or field containing the data to be measured.|
Specifying an offset of 0 will evaluate the expression on the current row.
Specifying a negative offset number makes the Above function work like the Below function with the corresponding positive offset number.
By specifying a third argument count greater than 1, the function will return a range of count values, one for each of count table rows counting upwards from the original cell.
In this form, the function can be used as an argument to any of the special range functions. Range functions
If the table is one-dimensional or if the qualifier TOTAL is used as argument, the current column segment is always equal to the entire column.
On the first row of a column segment, a
Recursive calls will return
Examples and results:
In the screenshot of the table shown in this example, the table visualization is created from the dimension
For the column labeled
The measure labeled
The table also shows more complex measures: one created from Sum(Sales)+Above(Sum(Sales)) and one labeled
In the screenshots of tables shown in this example, more dimensions have been added to the visualizations:
In the following screenshot of table visualization for Example 2, the last-sorted dimension is Month, so the Above function evaluates based on months. There is a series of results for each Product value for each month (Jan to Aug) - a column segment. This is followed by a series for the next column segment: for each Month for the next Product. There will be a column segment for each Customer value for each Product.
In the screenshot of table visualization for Example 3, the last sorted dimension is Product. This is done by moving the dimension
The Above function can be used as input to the range functions. For example: RangeAvg (Above(Sum(Sales),1,3)).
Data used in examples:
LOAD * INLINE [
crosstable (Month, Sales) LOAD * inline [
] (delimiter is '|');
To get the months to sort in the correct order, when you create your visualizations, go to the Sorting section of the properties panel, select Month and mark the checkbox Sort by expression. In the expression box write Monthnumber.