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:
if([Measure1] is null)
then (null)
else(
rank([Measure1] asc within set [Store])
)
Model i'm using is DMR
Any suggestion are appreciated
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...
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:
rank ( numeric_expression [ ASC|DESC ] [ tuple member_expression { , member_expression } ] within set set_expression )
Relational:
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
You could try something like the following:
rank( if( [Measure1] is null ) then ( -99999999 ) else( [Measure1] ) within set [Store] )
THANKS for the suggestion, it worked great.
Had to use a positive number, to get it to be ranked after [Measure1]
ie.
rank( if( [Measure1] is null ) then ( 99999999 ) else( [Measure1] ) asc within set [Store] )
Thank you thank you thank you :-)