cancel
Showing results for 
Search instead for 
Did you mean: 

Top 10 even if there are duplicate values in webi.

former_member334960
Participant
0 Kudos
1,075

Hi All,

I have are a requirement to show only top 10 records. But when the values are duplicated, we can see more than 10 records.

Example : image.png Ranking is based on Total field.

Here we see we are getting 12 records since few values are dulplicated. But I need to show only top 10. Can someone please help on this.

Thanks

Poojitha

Accepted Solutions (0)

Answers (6)

Answers (6)

0 Kudos

i am also facing the same issue.

amitrathi239
Active Contributor
0 Kudos

2 records for rank 2

3 records for rank 4

9 records for rank 8

in this case for 14 rows, webi has to display 3 rows.which is line with result.i am not able to understand where is the issue.

former_member334960
Participant
0 Kudos

But we are looking for a workaround where webi can show 10 records. In my case if you see the example :

image1.png

VRC(2) is the running count of VRANK.

Vrank is the rank of Total2

VRC is the runningcount(VRank;Total2)

We should be able to show data till EPIC which is the top 10 records.

Is there any way for this. Please let me know if the details are not clear

amitrathi239
Active Contributor
0 Kudos

how many records are in the table?

as per your screen it's working fine as you have multiple records with same rank.

e.g for rank 8 records are there so running count will show only one record.

former_member334960
Participant
0 Kudos

Please see the table data below: There are 16 records.

image1.png

After I apply both the filters, I get only 5 records.

image.png

But I have to show 10 records.

Is there any way we can show 10 records even when the rank is same.

amitrathi239
Active Contributor
0 Kudos

use the below formula for a runningcount variable.

=RunningCount(([VRank]);([Query 1].[Total2]))
former_member334960
Participant
0 Kudos

I tried this, but now we are getting only 9 records.
First I applied filter where VRank is <=10 and then I applied another filter where VRunningCount=1.

Below is the report after applying filter on VRank.

image1.png

Below is the report after applying filter on VRank and VRunningCount

image.png -- here we are getting only 9 records.

Expectation is to show Top10 always.

former_member334960
Participant
0 Kudos

In scenarios where the data is like below, we are getting only 5 records.
image1.png

amitrathi239
Active Contributor
0 Kudos

follow the below steps.

Create V Rank=Rank([Total])

Apply filter on V Rank where less than equal to 10 to get top 10 records.

Next variable V Runningcount=runningcount([V Rank]);([V Rank]))

Filter on table where V Runningcount is equal to 1.

former_member334960
Participant
0 Kudos

Hi Amit,

I have first created a variable for Rank as below and applied filter on table where Rank<=10

=Rank([Query 1].[Total2])

and then created another variable

=RunningCount(([VRank]);([VRank]))

and when I filter on VRunning count=1, i get only one record.

image.png

0 Kudos

Hi,

You first need to fix the cause of the duplication before running the top 10 function. what is your source of data? is it excel or universe or bex? You have a duplication because either you allowed it in the table properties /settings in query ; or in your query , you included a field which has a different data for each row.

I would ensure that the dulplication is not allowed by looking at table settings/properties. Then , If your source is not excel , I would create a new query, which just contains the Incident field and Total and add it the table in the same report or new report of the same document depending of what you want. it would then aggregate the number of incidents per incident type. Try it and see.

if it works for you , please don't forget to accept the answer as correct and close it. 🙂