23 Replies Latest reply: Jul 7, 2011 2:07 AM by Kaushik Solanki

# Ranking in ascending order

Hi All,

I have a requirement wherein I have to do the ranking of values in ascending order.  For Example lower the values, higher the rank.

Value    Rank

1           1

3             2

8             3

10           4

Can you please suggest how to use the Rank function to get the desired result.

Regards,

Raghav

• ###### Ranking in ascending order

Hi All,

I tried using '  - ' minus symbol before the expression using Rank function and I got the desired result.  But there are lot of zero values in my data because of non availability of data.  Is it possible to get these zero values ignored in the Ranking exercise.

Regards,

Raghav

• ###### Re: Ranking in ascending order

Hi Raghav,

Here is the sample appln.find the attachment.

-Jai

• ###### Ranking in ascending order

Hi Jai,

Thanks for your reply.  I have lot of zero values in my data and want the Rank function to ignore zero values.  Can you suggest how to do it.

Regards,

Raghav

• ###### Ranking in ascending order

Hi Ravhav,

You can use below expression to ignore 0 in ranking (reverse ranking):-

SUM({\$<Value={"<>0"}>}Value)*-1)

FYI, I am multiplying the value with -1 to impose reverse ranking.

Rgds,

Dhananjay

• ###### Ranking in ascending order

Hi Dhananjay,

I tried below expression as suggested by you.... but the there is a rank assigned to Zero even now.

Rank((Sum({\$<[EcoCity]={"<>0"}[EcoCity])*-1),0,1)

Thanks and Regards,

Raghav

• ###### Ranking in ascending order

Hi ,

I think the expression wriiten is not syntactically correct..

Rank

((Sum({\$<Amt={"<>0"}>}Amt)*-1),0,1)   try this and checkkk

Regards,

Ajay

• ###### Re: Ranking in ascending order

Hi ,

I think the expression wriiten is not syntactically correct..

Rank

((Sum({\$<Amt={"<>0"}>}Amt)*-1),0,1)   try this and checkkk

Regards,

Ajay

Hi ,

I think the expression wriiten is not syntactically correct..

Rank

((Sum({\$<Amt={"<>0"}>}Amt)*-1),0,1)   try this and checkkk

Regards,

Ajay

Hi ,

I think the expression wriiten is not syntactically correct..

Rank

((Sum({\$<Amt={"<>0"}>}Amt)*-1),0,1)   try this and checkkk

Regards,

Ajay

• ###### Ranking in ascending order

Hi Ajay,

Tried the same.... still zero is not getting ignored.

Regards,

Raghav

• ###### Re: Ranking in ascending order

Hi,

In which chart you aare trying this..

If you are using Pivot then check whether the  "Supress Zero Values" is checked or not in Presentation tab.

can you attach the sample file...

Regards,

Ajay

• ###### Re: Ranking in ascending order

Hi,

In which chart you aare trying this..

If you are using Pivot then check whether the  "Supress Zero Values" is checked or not in Presentation tab.

can you attach the sample file...

PFA file

Regards,

Ajay

• ###### Ranking in ascending order

Hi,

If you change the expression with "

Rank((Sum({\$<SALES={"<>0"}>}SALES)*1),0,1) " it will work accordingly...

-Jai

• ###### Re: Ranking in ascending order

Hi,

I am unable to skip the rank for 0 value, but you can change the color  of the rank when the value is 0.

Have a look at the example

Regards,

Kashik Solanki

• ###### Re: Ranking in ascending order

Hi

I have modified,have a look.I hope it is working fine

-Jai

• ###### Re: Ranking in ascending order

did you ever solve this problem? I have the same one unfortunetly...

• ###### Re: Ranking in ascending order

HI,

Try this.

if(Sum (SALES) > 0, rank( sum( SALES ),0,1),0)

Regards,

Kaushik Solanki

• ###### Ranking in ascending order

Thanks!

But my problem now is that there's no number 1 even if it is the lowest one. ranking starts with 5 if there were 4 zero values in the range.

I'm coloring the values that are highest and lowest and i do this by hardcoding them right now. do you have a better suggestion for me?

Thanks again!

//A.

• ###### Re: Ranking in ascending order

Hi

Regards,

Kaushik Solanki

• ###### Re: Ranking in ascending order

here you go,

if you do changes and send it back i wont  be able to download it for some reson (probably firewall) so pleas write the expressions as long as you can!

Thanks!

• ###### Re: Ranking in ascending order

Hi,

I coudnt find anything starting from 5.

Please take a screen shot and post here.

Reagards,

Kaushik Solanki

• ###### Re: Ranking in ascending order

Do you see the column "4 år" that ranking starts with 5 and forth

• ###### Re: Ranking in ascending order

..and here's the screenshot =)

• ###### Re: Ranking in ascending order

i did solve the "not-starting-rank-from-one"-problem by using this expression instead;

rank(aggr(((sum(rate)*-1)),name,TimeName),0,1)

but now i still have a dynamic higest rank number that i need to higlight some how.

• ###### Re: Ranking in ascending order

HI,

Try this.

Click + button at rank expression.

Change the text color or the background.

Regards,

Kaushik Solanki