BottomPercent (PQL)

Returns a derived list of the BOTTOM elements, from the given list, that sum to a specified percentage of the total for the entire list.

  • Returned Output: List
  • Library: PQL \ Semantic \ List
  • Version: 2018.00.000
  • Compatibility: Pyramid Query Language (PQL) data sources

Syntax

BottomPercent( <List> , <Integer> , OPTIONAL <Data Point> )

* Click on the function's arguments above for more details on the input values.

Comments
  • List parameter should be a list or set of elements from an existing hierarchy in the model.
  • The integer value specifies the percentage (whole numbers 0-100) of the total, that the bottom elements must account for.
  • The data point or tuple is optional, and describes the values used to determine the order for the list. If not supplied, the default measure or the query context will determine this value instead.
  • For details on how to employ and use this function see the semantic calculation overview.

This function is like the MDX BottomPercent function; it calculates the bottom list of elements from a specified a list.

The BottomPercent function works by ordering the list from highest to lowest using the optional numeric data point (value) or default values. It then captures as many elements as needed to achieve the target percentage value of the total, by summing the numeric data points for each element in the list.

Examples

This example will take all the members in the Manufacturer attribute (listing), rank them from high to low based on August 2010 expenses, and then return as many elements as needed to achieve at least 15 percent (in Aug 2010 expenses) from the bottom:

BottomPercent( {AllMembers([manufacturers].[manufacturer])}, 15, ([measures].[data Expenses], [data].[dateKey].[aug 2010]) )

Using the grid below, this will return Princess, Nuke and Adihash (look at the third numeric column for percents of total):