I’m working on this for days and didn’t find it out yet, if somebody can help with it will be great.
How to use the calculated field in quicksight to show the top 4 suppliers based on hours worked and group the rest of the suppliers into a group called ‘others’?
I would use the rank function to calculate the rank for each supplier based on hours worked and then create another calculated field to put them in different groups. The exact calculated fields will depend on your dataset but this should give you an idea.
Supplier Rank:
rank([{Hours Worked} DESC], [], PRE_AGG)
Group:
ifelse({Supplier Rank} <= 4, 'Top 4', 'Others')
Hi @Theodoro_Fogagnoli , Suggestion from David should help you resolve it.
In case you need more assistance , please create a sample dashboard with sample dataset showing your problem state using Arena and post it here.(Details on using Arena can be found here - QuickSight Arena )
We will take a look once we have the sample from you.
This is the graph I’m working on, the intention is to show the top 4 by name in each legend and then group the rest within others and also show them in the legend.
HI @Theodoro_Fogagnoli
do you need it for calculation or just in the visual?
But you visual looks pretty much as what you describe. Only thing missing is the sort within the bars.
BR
Hi @ErikG , thanks for your help in advance.
I’m having problems, because after creating the two calculated fields “Supplier Rank” and “Group”, when adding to the visual, it is not showing the top 5 individually (I mean top 5 names individually, Like Company 1, company 2, company 3,company 4, company 5 and them the rest in the group OTHERS)
@Theodoro_Fogagnoli
You actually didn’t need to create any calculated fields. Just put the number of hours in the Value field and sort in descending order. Then format the visual to only show 4 bar segments like what @ErikG is showing and QuickSight will group all other suppliers in an “Other” segment.
After you do that, you should probably disable sorting on your y-axis because if your users change the sort order, your visual will show bottom 4 + other instead of top 4 + other.
Hi, @David_Wong I’ve tried what you said and it still showing the 5 top companys and no “Others” group in the legends under the graph, I’m doing something wrong?
I’m trying to upload the picture of it, but it is not allowing me.
Hi @Theodoro_Fogagnoli
based on your screenshot it looks like the are no Top5 values or is the value to low to show on the visual?
If you are not able to share images maybe you can create the analysis in Arena?
BR
Hi @ErikG, as you can see, I did exactly what you said, it shows only the 5 companys, but it isnt showing the rest inside group others.
And there are more groups?
Can you create the same visual as a table and check there is more to show?
Yes there are many companys, around 30 if I’m guessing right, the thing is that the information are all confidential that is the problem to put on a table and show you.
You don’t have to show me. But you can check for yourself in the table visual.
I already did, it is showing 33 diferent companyes, maybe it needs a calculated field or you think I’m doing something wrong?
