Ranking Function in WEBI?

[Former Member]

Hi Experts,

I have a weird requirement in WEBI. i have three tables it contains Dimension and Measure.

Now my requirement is if its a Rank one then i need to put these record into one table after that if its a Rank 2 then i need to display only second record into another table. Whenever i am applying Rank 2 it is bringing two records i.e., Rank 1 and Rank 2 but i need to get only second record (i.e., Rank 2 record).

Is there a way to achieve this?

Thanks

Sudha

[former_member183241]

Hi Sudha,

Can u pls attached screen shot or elaborate the scenario.

Thanks,

Kashif

[Former Member]

Hi ,

Lets say for example i have below data set.

Cust Id	Amount
1	100
2	200
3	600
4	300
5	50
6	800
7	700
8	500
9	1000
10	1100

From this if i apply rank 1 on Amount filed i am getting below record

Cust Id	Amount
10	1100

If i apply rank 2 on Amount Filed i am getting Below records

Cust Id	Amount
10	1100
9	1000

Now My requirement is when i apply Rank 2 i need to get below record only.i.e.,

Cust Id	Amount
9	1000

How can i achieve this?

Thanks

Sudha

[former_member183241]

Hi Sudha,

Follow below steps.

1)Create New Variable on Amount filed with Formula '=Rank([Amount])' refer below screen shot

2) Add Filter by a New Input Control on variable.

3) Now you get desire output. change Rank in filter and get amount accordingly.

Thanks,

Kashif Khan

[Former Member]

Hi Kashif,

I think you didn't get my question.

I know how to apply ranking in WEBI. If you read my previous reply you will get what my requirement is.

Thanks

Sudha

[former_member183241]

Hi Sudha,

Rank means it give output base on Rank, For Example If we need Top 10 or bottom 10 Customer List then we apply Rank for Top 10 or Bottom 10. It give all 10 customer as output.

If you want to filter on specific Rank like your requirement that If select 1 it display top 1 Rank customer, If select 2 then display Top 2 Rank Customer only and as on.

This is as per my understanding, May be i am not able to understand your requirement.

Thanks,

kashif

[Former Member]

Hi Kashif,

If i select rank 2 it will bring top 2 Cust id's but my requirement is if i select rank 2 i need to get only rank 2 record not rank2 and rank 1 records.

So if i select Rank 2 i need to get only one record i.e., Rank 2 record not Rank 1 Rank 2 combined.

Thanks

Sudha

[former_member183241]

Hi,

If i select 2 then get only one record that is Rank-2 refer below screen shot

If i select 3 then get only one record that is Rank-3 refer below screen shot

Thanks,

[mhmohammed]

Hi Sudha,

I did the below and it works (with one minor issue that I didn't spend time on) for your requirement.

• Create variable v_Rank = Rank([Amount])
• Create variable v_Rank1 =If([v_Rank]=1) Then 1 Else 0 (This is used as a flag)
• Create variable v_Rank2 =If([v_Rank]=2) Then 1 Else 0 (This is used as a flag)
• Create variable Select a Rank =1 (We'll create Input Control on this object, so, 1 becomes default selection)
• Create a Input Control  -> Single Value -> Entry Field and you'll see that the Default value(s) is 1

• Now, you'll see that Select a Rank value will be equal to the value you enter in the input control.
• Create a final variable v_Displayed Rank =If([Select a Rank]=1) Then [Cust ID] Where ([v_Rank1]=1) ElseIf([Select a Rank]=2) Then [Cust ID] Where ([v_Rank2]=1)
• Add v_Displayed Rank to the table and filter on it as v_Displayed Rank Is Not Null.
• Now when you enter 1 in the filter and click OK, you'll see Rank1 row is displayed and when you enter 2 in the filter and click OK, you'll see Rank2 is displayed.

Note: As a test, add all the variables we created to the table including Cust ID & Amount to see what each variable does.

That's it, try if its working fine. Let me know if this helps and/or if you have any questions.

Thanks,

Mahboob Mohammed