Returns the smallest possible subset of a set for which the total results of a numeric evaluation are at least a given percentage. The result set is returned with elements listed from smallest to largest.
BottomPercent ( set, percentage, numeric_value_expression )
set | The set from which to select the bottom-percentile elements. |
percentage | The percentile. This argument must be a value between 0 and 100. |
numeric_value_expression | The expression that defines the selection criteria. |
This function ignores negative and missing values.
The following query returns data for products making up the lowest 5th percentile of all product sales in the Sample Basic database.
WITH SET [Lowest 5% products] AS 'BottomPercent ( { [Product].members }, 5, ([Measures].[Sales], [Year].[Qtr2]) )' MEMBER [Product].[Sum of all lowest prods] AS 'Sum ( [Lowest 5% products] )' MEMBER [Product].[Percent that lowest sellers hold of all product sales] AS 'Sum ( [Lowest 5% products] ) / [Product] ' SELECT {[Year].[Qtr2].children} on columns, { [Lowest 5% products], [Product].[Sum of all lowest prods], [Product], [Product].[Percent that lowest sellers hold of all product sales] } on rows FROM Sample.Basic WHERE ([Measures].[Sales])
In the WITH section,
[Lowest 5% products]
consists of those products accounting for the lowest 5 percent
of sales in the second quarter. This set includes Birch Beer, Caffeine Free Cola, Strawberry, Sasparilla, and Vanilla Cream.[Product].[Sum of all lowest prods]
, is used to show the sum of the sales of the products with sales in the lowest fifth percentile.[Product].[Percent that lowest sellers hold of all product
sales]
, is used to show, for each month, how the sales of lowest-selling
products compare (as a percentage) to sales of all products in the Product dimension.This query returns the following grid:
Apr | May | Jun | |
---|---|---|---|
Birch Beer | 954 | 917 | 1051 |
Caffeine Free Cola | 1049 | 1065 | 1068 |
Strawberry | 1314 | 1332 | 1316 |
Sasparilla | 1509 | 1552 | 1501 |
Vanilla Cream | 1493 | 1533 | 1612 |
Sum of all lowest prods | 6319 | 6399 | 6548 |
Product | 32917 | 33674 | 35088 |
Percent that lowest sellers hold of all product sales |
0.192 | 0.194 | 0.187 |
©2004 Hyperion Solutions Corporation. All Rights Reserved. http://www.hyperion.com |