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

Question

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!

Helping Hand.. Helping Hand.. · Accepted Answer · 2014-05-15T05:37:55

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] )

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

1 Answers