Ranking data using formula

#1

Hi folks,


First up, please don't suggest I do this in the cube!


I'm trying to add a formula to the data analysis panel for one of my visualisations, in order to calculate the rank of each row. I need the rank so I can colour my visualisation accordingly (it's a diagram). There doesn't appear to be a rank function that you can use in formulae built-in to Dundas.


I've tried this gem, but to no avail:


if ($MyMeasure$ == RANKVAL($MyMeasure$,1)) {
return 1
}
if ($MyMeasure$ == RANKVAL($MyMeasure$,2)) {
return 2
}

...

return 12


The problem is that if there are only, say, 5 results in the data, RANKVAL returns something that is null, or empty, or something. Can I check for this somehow? Is there any other, less filthy, way of doing it - without changing the cube?


Btw, I don't want to modify the cube because it is used in other visualisations, and I'm trying to keep my re-work to a minimum. If there is no other possible way to do this, then I guess that's my decision made.


Thanks,

Rob


#2

Not 100% sure of what your issue is.


I think this might help,


Try another calculated measure formula that works off your gem, and calculates

if($gem$ > 0) return $gem$;

else return <whatever you want to return>



#3

Hi David,


Thanks for the reply. Sorry, I should have explained that $MyMeasure$ is a distinct count of records coming back from the database, grouped by some other hierarchy (let's call this ProductCategory). When I said I wanted a rank, what I meant was that the ProductCategory with the highest distinct of $MyMeasure$ should be ranked '1', then the next highest should be 2, etc.


Does that make any more sense?


Thanks,

Rob.

#4

I see.

I did have a need of this and I just gave up and scripted it, so I made a script that read in all the data, sorted it and then returned the hierarchy value.

Even more dirty than yours it would seem


Sorry I can't help more

#5

Hi David,


Appreciate your reply, and I'm glad it isn't just me missing something obvious! Scripting seems to be the answer to so many Dundas-related questions.


Cheers,

Rob.

#6

Hello Rob,


I've got a solution that requires the metric set to be sorted in descening order of your ranking measure. If there's a reason you can't have this, let me know and I can look at some other options. Here it is:


1. Set a descending sort on the ranking measure.


2. Add a formula measure called "One" and for the expression, put this:

return 1;

3. Add a second formula called Rank with the following expression:

return CMLTOTAL($One$);
#7

Hi Davide,


That is a pretty cunning solution!


However... We actually need rows with the same value to have the same rank:


ProductID OrderQty Rank

123 2000 1

126 1200 2

325 1200 2

96 800 3

...


Any ideas if this is possible?


Many thanks,

Rob

#8

Hi Rob,


With those additional requirements, the method I suggested won't work unfortunately. Other than waiting for a feature to be released for this particular case I see two options that you could pursue:


1. Use the data cube Rank transform. Since this transform will simply add a Rank column and won't change any existing columns it should not disrupt any other visuals that depend on the cube. If you need extra peace of mind, you also have the option to create a separate copy of the cube to be used for this specific purpose; that will guarantee that your existing visuals will not be affected.


2. Create a custom formula function by writing an extension.