CODEWITHSUNDEEP
mdxpentahoolapolap-cubemondrian
jphorta
jphorta

Reputation: 2174

MDX query with TOPCOUNT between date range returning a top with some empty values

I'm using a MDX query to get the top 10 liked Products between two dates. Oddly, the result is composed by some Products with likes within that date range, and some without any likes at all. Here is the query:

SELECT
{ [Measures].[Likes] } ON COLUMNS,
{ TOPCOUNT([Products].[Name].Members, 10, [Measures].[Likes]) } ON ROWS
FROM [Likes]
WHERE ( [Date].[2014].[3].[29]:[Date].[2014].[4].[5] )

Here are the results:

               [Measures].[Likes]
[Product].[XX]
[Product].[XX]
[Product].[XX]          139
[Product].[XX]
[Product].[XX]           1
[Product].[XX]
[Product].[XX]
[Product].[XX]          125
[Product].[XX]          111
[Product].[XX]           1

If I change the top limit to 20, for example, the results will have more products with likes but also with more empty ones, and not ordered (like a top usually is).

Using NON EMPTY makes the query return only 5 results instead of 10, and still not ordered.

Thanks!

Upvotes: 1

Views: 1209

Answers (1)

Helping Hand..
Helping Hand..

Reputation: 2440

Try this query

SELECT { [Measures].[Likes] } ON 0, Head(ORDER([Products].[Name].Members,[Measures].[Likes], DESC), 10) ON 1 FROM [Likes] WHERE ( [Date].[2014].[3].[29]:[Date].[2014].[4].[5] )

Upvotes: 1

Related Questions