1
Vote

BottomCountWithTies returns wrong values

description

Set to sort:
6
6
6
12
6
6
14.99
2.99
6
2.99
6
6
17.94


The returned result for bottom 5 vaules with BottomCountWithTies:
2.99
2.99
6
6
6
6
6
6
6
6
17.94

Expected result:
2.99
2.99
6
6
6
6
6
6
6
6

In any case, the 17.94 is out of order. The next figure after the 6's should be 12.

All the records:
Image


BottomCountWithTies with 5:
Image

BottomCountWithTies with 11 (Correct):
Image


EDIT: The MDX Query:
WITH
SET OrderedItems2 AS ASSP.BottomCountWithTies(
                                                                NonEmpty([Item].[Item ID].[Item ID].Members, [Measures].[Revenue]),
                                                                5, [Measures].[Revenue])
SELECT
{[Measures].[Revenue]} ON COLUMNS
,{
OrderedItems2

} ON ROWS


FROM [ItemPurchase]
WHERE ([Time].[Month].[Month].&[2013-02-01T00:00:00])

comments

dtroyz wrote Apr 24, 2013 at 1:47 AM

I had a look at the code (for TopCountWithTiesInternal). I think you'll be better off just sorting the set.
If you're worried about the performance, you can:
  1. Use selection algorithm to get the top K - O(N)
  2. Get the top K using the pivot you for from step 1
  3. Sort them in O(k log k)
  4. Search the rest of the (N-K) elements beyond the pivot for the same value as the top K'th element to get all the ties. - O(N-K)
See:
http://stackoverflow.com/a/4956638/36777
http://stackoverflow.com/a/4084513/36777

furmangg wrote Apr 24, 2013 at 4:27 AM

Thanks for reporting this. We'll have to look into this.

Can you provide your MDX query. I just want to check that there's not some small mistake in your query such as a filter only being applied to the Revenue number returned on screen but not to the revenue number passed into the ASSP function.

dtroyz wrote Apr 24, 2013 at 5:11 AM

Sure. I've edited the ticket.
I don't why it formatted it that way.

dtroyz wrote Apr 24, 2013 at 6:06 AM

BTW, The following MDX is giving the correct results, so it might be a workaround:

WITH
SET OrderedItems_WithoutTies AS BottomCount(
                                                                NonEmpty([Item].[Item ID].[Item ID].Members, [Measures].[Revenue]),
                                                                 5, [Measures].[Revenue])
SET OrderedItems1 AS Filter(
                                                                                                NonEmpty([Item].[Item ID].[Item ID].Members, [Measures].[Revenue]),
                                              [Measures].[Revenue] <=MAX(OrderedItems_WithoutTies , [Measures].[Revenue])
                                            )


SET OrderedItems2 AS ORDER( OrderedItems1 , [Measures].[Revenue], BASC)


SELECT
{[Measures].[Revenue]}ON COLUMNS
, {OrderedItems2} ON ROWS
FROM [ItemPurchase]
WHERE ([Time].[Month].[Month].&[2013-02-01T00:00:00])