Rank() Function Question

[system]

I need the rank function to do a dense rank in Webi. Currently the default behavior of the Rank() function returns the following ranking: 1,2,3,3,5. If two values tie for 3rd place, the next number in sequence returned is 5. I need it to return 4. Does anyone know a workaround to get this done?

I saw one possible solution listed on the boards, but I get Context error when I try to implement it.

Thanks so much!

---

jmacvittie (BOB member since 2008-09-10)

[system]

I’m not sure if there is a solution in rank function itself but you can solve it with a trick as follows:

Create a new variable based on ranking value as follows

if ([Rank Value] = previous([Rank Value]); [Rank Value];[Rank Value]+1)

Hope this helps…

---

ERE (BOB member since 2008-06-04)

[system]

If your database supports it there is a Dense_Rank function available to your universe designer. You might find that suitable for you needs.

---

dessa (BOB member since 2004-01-29)

[system]

Hi,

Even i had the issue of displaying theranking has 1,2,3,3,5.
And i made a small change to the formula given by ERE and it worked fine and now i get the ranking as 1,2,3,4,5 .Please see the quote for change i ahve done

=If([Rank]=Previous([Rank]);[Rank]+1;[Rank])

But if the ranking is dispayed is 1,2,3,3,3

then after applying the above formula i get the ranking as 1,2,3,4,4.
But i should get it dispalyed as 1,2,3,4,5

Can anyone suggest the change i have to do the formula…

---

breeze_08 (BOB member since 2008-04-03)

[system]

This can be done(but is not good. I know)
First sort out the column in descending order. Add a new variable

Rank=LineNumber()-1

and add this to the table.

---

Jansi (BOB member since 2008-05-12)

[system]

Hi, i am not able to achieve the desired resulys with the formula LineNumber()-1.

i think Dense_Rank fumction will be solution for this issue. Can anyone suggest how to use this in the Universe Designer.

---

breeze_08 (BOB member since 2008-04-03)

[system]

Bump*

I also need to force WebI to use oracle’s DENSE_RANK () instead of RANK() for my top query.

Any idea?

---

prinzartus (BOB member since 2006-09-07)

[system]

Go and ask your Designer to add it to the universe.

---

dessa (BOB member since 2004-01-29)

[system]

Hi,

i have DENSE_RANK() in the universe. and below is the steps that i followed.
Steps:
Create a object and in thhe defintion part from the Functions tab under Number, there is function like

DENSE_RANK() OVER (PARTITION BY () ORDER BY ())

in the PARTITION BY and ORDER BY clause define the objects as per the requirement.

Please see the sample code which i have defined

DENSE_RANK() OVER (PARTITION BY (@Select(Calendar\Quarter)) ORDER BY (@Select(Ranking\Number of Prints)))

where @Select(Calendar\Quarter) is DIM_CALENDAR.CAL_QTR
and @Select(Ranking\Number of Print) is sum (FACT_OFFER_PRINT.PRINT_QTY)

i got the desired results as below

Quarter Number of Prints Ranking
3 2 1
3 2 1
3 2 1
3 5 2

---

breeze_08 (BOB member since 2008-04-03)

[system]

DENSE_RANK() OVER (PARTITION BY (@Select(Calendar\Quarter)) ORDER BY (@Select(Ranking\Number of Prints)))

where @Select(Calendar\Quarter) is DIM_CALENDAR.CAL_QTR
and @Select(Ranking\Number of Print) is sum (FACT_OFFER_PRINT.PRINT_QTY)

You can tick the “show Object SQL” radio button when editing the select statement of the object to show the full sql generated by the object.

---

dessa (BOB member since 2004-01-29)

[system]

Hi

---

Fatih (BOB member since 2015-12-23)

[system]

=If [Rank]=Previous([Rank]) And [Rank]=Previous([Rank];+2) And [Rank]=Previous([Rank];+3) Then [Rank]+3
ElseIf [Rank]=Previous([Rank]) And [Rank]=Previous([Rank];+2) Then [Rank]+2
ElseIf [Rank]=Previous([Rank]) Then [Rank]+1

---

Fatih (BOB member since 2015-12-23)

[system]

the easy and dirty solution is . do you running count on Rank function. that will give you ranks as 1,2,3,4 ,5 etc.

---

Caitanya.Candra (BOB member since 2008-08-04)