Bottom 5 query

[RandomHunter]

Hi all,

I'm trying to show the bottom 5 products by revenue. I'm working in a dimensional model but bottomCount() does not give the correct values. So I used the rank() function to create a ranking.
I multiplied revenue with -1 to reverse the order and wrote this expression: rank([revenue] within set children([total product]). This gives me a a ranking = [rank], I filtered ranking to show me the 5 values I need: [rank] < 6. However some of the products don't have revenue yet and their value is 0. But these zero's all get the same ranking number and the next ranking number won't follow on this number. I'll try to visualize it below.

Product		Revenue*-1		Rank
A		10		1
B		11		2
C		0		3
D		0		3
E		0		3
F		-10		6

As you can see, for al the zero's the rank is 3, because they're all the same. But for the next product that hasn't got a revenue of 0, the rank is 6.If i put a filter on the query: [revenue] <> 0, the zero values will disappear but the ranking will remain. Product F will still be ranked as 6. Does anyone have a solution for this?

[MFGF]

I'm working in a dimensional model but bottomCount() does not give the correct values.

BottomCount() sounds like the exact solution for this. What values does it return if not the bottom 6? What arguments did you use?

Cheers!

MF.

[RandomHunter]

Thanks,

I made a new data item ([Bottom] like so: bottomCount(children([Total Product]),5,[Revenue]). This gave back 5 products for the selected country, ordered ascending and 4 out of 5 were zero. Which is correct as there are no negatives there. But when I filtered on revenue <> 0, only the one product remained. And this was not a correct product. It had a value somewhere close to the top. All the count function did was sort the products by name, ascending. The same happened when I used topCount.

[MFGF]

...But when I filtered on revenue <> 0, ...

You filtered? Using a detail filter? Well, there's your problem :)

Detail filters are a relational concept, and often return incorrect results in a dimensional report. You need to modify the expression of your [Bottom] item to include a filter() function to return members with a non-zero Revenue for use in the BottomCount() function.

eg: bottomCount (filter(children([Total Product]), [Revenue] > 0), 5, [Revenue])

Cheers!

MF.

[RandomHunter]

Thank you, MFGF  :)

The query works now but when I try to show these products in a pie chart I get the message "No Data Available". Any thoughts on why this happens?
It's also not possible for me to put children([Total Products) in the series, instead, it insert a part of the childeren and than other([Total Products])..