on 2019 Jul 10 3:56 AM
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
Request clarification before answering.
i am also facing the same issue.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
But we are looking for a workaround where webi can show 10 records. In my case if you see the example :
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Please see the table data below: There are 16 records.
After I apply both the filters, I get only 5 records.
But I have to show 10 records.
Is there any way we can show 10 records even when the rank is same.
use the below formula for a runningcount variable.
=RunningCount(([VRank]);([Query 1].[Total2]))
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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.
In scenarios where the data is like below, we are getting only 5 records.
image1.png
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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. 🙂
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
49 | |
10 | |
8 | |
6 | |
5 | |
5 | |
4 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.