Bottom

Bottom() evaluates an expression at the last (bottom) row of 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 bottom row. For charts other than tables, the evaluation is made on the last row of the current column in the chart's straight table equivalent.

Syntax:  

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

Return data type: dual

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 up n rows above the bottom row.

Specifying a negative offset number makes the Bottom function work like the Top function with the corresponding positive offset number.

count

By specifying a third parameter count greater than 1, the function will return not one but a range of count values, one for each of the last count rows of the current column segment. 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

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

Customer Sum(Sales) Bottom(Sum(Sales)) Sum(Sales) + Bottom(Sum(Sales)) Bottom offset 3
  2566 757 3323 3105
Astrida 587 757 1344 1126
Betacab 539 757 1296 1078
Canutility 683 757 1440 1222
Divadip 757 757 1514 1296
The table chart for Example 1.

In the representation of the table chart shown in this example, the table is created from the dimension Customer and the measures: Sum(Sales) and Bottom(Sum(Sales)).

The column Bottom(Sum(Sales)) returns 757 for the all rows because this is the value of the bottom row: Divadip.

The table also shows more complex measures: one created from Sum(Sales)+Bottom(Sum(Sales)) and one labeled Bottom offset 3, which is created using the expression Sum(Sales)+Bottom(Sum(Sales), 3) and has the argument offset set to 3. It adds the Sum(Sales) value for the current row to the value from the third row from the bottom row, that is, the current row plus the value for Betacab.

Example: 2

In the representations of table charts shown in this example, more dimensions have been added to the charts: Month and Product. 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 QlikView. QlikView evaluates the functions based on the column segments that result from the dimension that is sorted last. The column sort order is controlled under Sort and is not necessarily the order in which the columns appear in a table.

In the first table, the expression is evaluated based on Month, and in the second table it is evaluated based on Product. The measure End value contains the expression Bottom(Sum(Sales)). The bottom row for Month is Dec, and the value for Dec both the values of Product shown in the table is 22. (Some rows are not shown, to save space.)

Customer Product Month Sum(Sales) End value
      2566 -
Astrida AA Jan 46 22
Astrida AA Feb 60 22
Astrida AA Mar 70 22
... ... ... ... ...
Astrida AA Sep 78 22
Astrida AA Oct 12 22
Astrida AA Nov 78 22
Astrida AA Dec 22 22
Astrida BB Jan 46 22
First table for Example 2. The value of Bottom for the End value measure based on Month (Dec).
Customer Product Month Sum(Sales) End value
      2566 -
Astrida AA Jan 46 46
Astrida BB Jan 46 46
Astrida AA Feb 60 60
Astrida BB Feb 60 60
Astrida AA Mar 70 70
Astrida BB Mar 70 70
Astrida AA Apr 13 13
Astrida BB Apr 13 13
Second table for Example 2. The value of Bottom for the End value measure based on Product (BB for Astrida).

Please refer to Example: 2 in the Above function for further details.

Example: 3

Result

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

In the arguments for the Bottom() 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 starting with the row above the bottom row in the column segment (because offset=1), and the two rows above that (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
659.67
659.67
659.67

Monthnames:

LOAD * INLINE [

Month, 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 (Month, 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 '|');

To get the months to sort in the correct order, when you create your charts, go to the Sort tab of the chart properties, and mark the checkbox Expression under Sort by. In the expression box write Monthnumber.

See also:

 

Did this information help you?

Thanks for letting us know. Is there anything you'd like to tell us about this topic?

Can you tell us why it did not help you and how we can improve it?