How to Calculate Rank for Data Items having Similar Values

[Prerna Chaudhary]

 Hi All,

We need to display top 5 markets based on Total Count Values. The data that we have is in the following format:

Category   Market   Count   Rank
A   Market 1     3   1
B   Market 1     3   1
C   Market 1     1   1
A   Market 2     3   1
B   Market 2     1   1
C   Market 2     3   1
A   Market 3     5   3
B   Market 3     1   3
A   Market 4     1   3
B   Market 4     5   3
A   Market 5     2   5
C   Market 5     3   5
B   Market 6     1   5
C   Market 6     4   5

For each market, there are 3 categories i.e. A, B and C. 
We  need to show top 5 ranks based on total Count i.e. A + B+ C.

For ranking I have used rank ([Count] at [MARKET]) which is working fine but the issue is in case multiple markets have same total count, then the output will have more than 5 markets.

Like in the above output, we are showing Rank 1 to 5 but there are total 6 markets.For ex: Market 1 and Market 2 have same Count ie 7.
So how can we restrict the output to only 5 markets.


Also I had used had also used  created a 3rd data item "Running Count" as running count([Rank]) and added a filter of "Running Count<=5", But it did not work.

So please  let me  know how to display only 5 markets in this scenario.

Thanks and regards

[BigChris]

In your example, which of Market 1 and Market 2 do you want to display, and how are you going to choose between them? Given your data, you'd have the same issue the top 1, top 3 or top 5 records. Essentially, Market 1 and Market 2 are joint first, so if you were showing the top 1 record, I'd suggest you'd need to show them both as they're effectively tied for first place.

[Prerna Chaudhary]

In this case, what we can do is we can use categories for tie breaking.

Say we use , Category C.

Now we have 6 Markets and we need to show only 5. Between Market 5 and Market 6 , category C value is greater in Market 6.

So we select Market 6 and ignore Market 5, as I just want to show top 5 markets.

So how can i achieve this.

[Rahul Ganguli]

Hi Prerna,

Attached is the XML for report. You can use it with any model and SQL server database. If samples are configured in your COgnos instance then it should directly work.

This report will produce an output as below
Category Market Count Rank
A Market 1 3 2
B Market 1 3 2
C Market 1 1 2
A Market 2 3 1
B Market 2 1 1
C Market 2 3 1
A Market 3 5 3
B Market 3 1 3
A Market 4 1 3
B Market 4 5 3
A Market 5 2 6
C Market 5 3 6
B Market 6 1 5
C Market 6 4 5

Let me know if you need any other help.

Regards,
Rahul