Rank ascending excluding NULL values

[Deep750]

Hello Gurus.
I have a report where I need to rank two columns, i.e. current year and current month.
There are several rows with NULL values, and when I use rank function it ranks all the NULL values as one (1) and the values starts after that. The report has typically 30 Nulls, making the rank start at 31.
Is it any possibility to make the rank function ignore the NULL values? Or any other way to make it work?

the expression i'm using is:

Code Select Expand

if([Measure1] is null)
then (null)
else(

rank([Measure1] asc within set [Store])
)

Model i'm using is DMR

Any suggestion are appreciated

[Ziid]

Hi

Because you wrote "any suggestion..." I would just like to make a comment. Does Rank-function even work properly with DMR? I think you should use a function called "TopCount". As I understand, Rank only works within relational models...

[Deep750]

Thanks for the reply.
I don't see how topCount can work in the scenario when I need to get the postion/rank each store has regardin the measures.
Any suggestion?

----------------

Regarding Rank
ie.
Dimensional:

Code Select Expand

rank ( numeric_expression [ ASC|DESC ] [ tuple member_expression { , member_expression } ] within set set_expression )


Relational:

Code Select Expand

rank ( expression [ ASC|DESC ] { , expression [ ASC|DESC ] } [ at expression { , expression } ] [ <for-option> ] [ prefilter ] )
rank ( [ distinct ] expression [ ASC|DESC ] { , expression [ ASC|DESC ] } [ <for-option>] [ prefilter ] )
<for-option> ::= for expression { , expression }|for report|auto

[Greg]

You could try something like the following:

Code Select Expand

rank( if( [Measure1] is null ) then ( -99999999 ) else( [Measure1] ) within set [Store] )

[Deep750]

THANKS for the suggestion, it worked great.
Had to use a positive number, to get it to be ranked after [Measure1]
ie.

Code Select Expand

rank( if( [Measure1] is null ) then ( 99999999 ) else( [Measure1] ) asc within set [Store] )

Thank you thank you thank you :-)