Skip to main content

Below - chart function

Below() evaluates an expression at a row below 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 below. For charts other than tables, Below() evaluates for the row below the current column in the chart's straight table equivalent.

Syntax:  

Below([TOTAL] expr [ , offset [,count ]])

Return data type: dual

Arguments:  

Arguments
Argument Description
expr The expression or field containing the data to be measured.
offset

Specifying an offsetn, greater than 1 moves the evaluation of the expression n rows further down from the current row.

Specifying an offset of 0 will evaluate the expression on the current row.

Specifying a negative offset number makes the Below function work like the Above function with the corresponding positive offset number.
count

By specifying a third parameter count greater than 1, the function will return a range of count values, one for each of count table rows counting downwards from the original cell. In this form, the function can be used as an argument to any of the special range functions. Range functions
TOTAL

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.

Defining the aggregation scope

On the last row of a column segment, a NULL value is returned, as there is no row below it.

Note: A column segment is defined as a consecutive subset of cells having the same values for the dimensions in the current sort order. Inter-record chart functions are computed in the column segment excluding the right-most dimension in the equivalent straight table chart. If there is only one dimension in the chart, or if the TOTAL qualifier is specified, the expression evaluates across full table.
Note: If the table or table equivalent has multiple vertical dimensions, the current column segment will include only rows with the same values as the current row in all dimension columns, except for the column showing the last dimension in the inter-field sort order.

Limitations:  

Recursive calls will return NULL.

Examples and results:  

Example 1:  

Table visualization for Example 1

Table visualization for Example 1.

In the table shown in screenshot for Example 1, the table visualization is created from the dimension Customer and the measures: Sum(Sales) and Below(Sum(Sales)).

The column Below(Sum(Sales)) returns NULL for the Customer row containing Divadip, because there is no row below it. The result for the row Canutility shows the value of Sum(Sales) for Divadip, the result for Betacab shows the value for Sum(Sales) for Canutility, and so on.

The table also shows more complex measures, which you can see in the columns labeled: Sum(Sales)+Below(Sum(Sales)), Below +Offset 3, and Higher?. These expressions work as described in the following paragraphs.

For the column labeled Sum(Sales)+Below(Sum(Sales)), the row for Astrida shows the result of the addition of the Sum(Sales) values for the rows Betacab + Astrida (539+587). The result for the row Betacab shows the result of the addition of Sum(Sales) values for Canutility + Betacab (539+683).

The measure labeled Below +Offset 3 created using the expression Sum(Sales)+Below(Sum(Sales), 3) has the argument offset, set to 3, and has the effect of taking the value in the row three rows below the current row. It adds the Sum(Sales) value for the current Customer to the value from the Customer three rows below. The values for the lowest three Customer rows are null.

The measure labeled Higher? is created from the expression:IF(Sum(Sales)>Below(Sum(Sales)), 'Higher'). This compares the values of the current row in the measure Sum(Sales) with the row below it. If the current row is a greater value, the text "Higher" is output.

Tip: This function can also be used in charts other than tables, for example bar charts.
Tip: For other chart types, convert the chart to the straight table equivalent so you can easily interpret which row the function relates to.

For charts with more than one dimension, the results of expressions containing the Above, Below, Top, and Bottom functions depend on the order in which the column dimensions are sorted by Qlik Sense. Qlik Sense evaluates the functions based on the column segments that result from the dimension that is sorted last. The column sort order is controlled in the properties panel under Sorting and is not necessarily the order in which the columns appear in a table.Please refer to Example: 2 in the Above function for further details.

Example 2

Example 2:  

 Result

The Below function can be used as input to the range functions. For example: RangeAvg (Below(Sum(Sales),1,3)).

In the arguments for the Below() function, offset is set to 1 and count is set to 3. The function finds the results of the expressionSum(Sales) on the three rows immediately below the current row in the column segment (where there is a row). These three values are used as input to the RangeAvg() function, which finds the average of the values in the supplied range of numbers.

A table with Customer as dimension gives the following results for the RangeAvg() expression.
 

Astrida

Betacab

Canutility

Divadip:

659.67

720

757

-

Data used in examples:

Monthnames: LOAD *, Dual(MonthText,MonthNumber) as Month INLINE [ MonthText, MonthNumber Jan, 1 Feb, 2 Mar, 3 Apr, 4 May, 5 Jun, 6 Jul, 7 Aug, 8 Sep, 9 Oct, 10 Nov, 11 Dec, 12 ]; Sales2013: Crosstable (MonthText, Sales) LOAD * inline [ Customer|Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec Astrida|46|60|70|13|78|20|45|65|78|12|78|22 Betacab|65|56|22|79|12|56|45|24|32|78|55|15 Canutility|77|68|34|91|24|68|57|36|44|90|67|27 Divadip|57|36|44|90|67|27|57|68|47|90|80|94 ] (delimiter is '|');

See also: