SSAS FAQ MDX FAQ How do I find the bottom 10 customers with the lowest sales in 2003 that were not null?
How do I find the bottom 10 customers with the lowest sales in 2003 that were not null?
User Rating: / 0
PoorBest 
Written by Thomas Ivarsson   
Friday, 30 May 2008 00:35

How do I find the bottom 10 customers with the lowest sales in 2003 that were not null?

Simply using bottomcount will return customers with null sales. You will have to combine it with NONEMPTY or FILTER.
 
Query example:

SELECT { [Measures].[Internet Sales Amount] } ON COLUMNS ,
BOTTOMCOUNT(
NONEMPTY(DESCENDANTS( [Customer].[Customer Geography].[All Customers]
, [Customer].[Customer Geography].[Customer] )
, ( [Measures].[Internet Sales Amount] ) )
, 10
, ( [Measures].[Internet Sales Amount] )
) ON ROWS
FROM [Adventure Works]
WHERE ( [Date].[Calendar].[Calendar Year].&[2003] ) ;

 

Result will be:

 Internet Sales Amount
Ariana Peterson$2.29
Olivia Brown$2.29
Abigail L. Bennett$2.29
Natalie L. Bryant$2.29
Madison D. Lee$2.29
Lauren Miller$2.29
Stephanie B. Murphy$2.29
Cameron L. Rodriguez$2.29
Melanie Peterson$2.29
Alfredo Romero$2.29

Done.

< Prev   Next >