Show Top 2 clients and put rest added in 'Others' category

Question & Answer
, , ,
1

Hi! Please help on this.
I’m adding a sample dataset for my scenario, consider I have 4 clients, and each client has 2 projects and I want to show only top 2 clients based on revenue, rest of the clients should be shown as ‘others’.
Here I need to create cumulative revenue field first and then create a rank field based on cumulative revenue field, then create ifelse based on rank field to only show top 2 client names and put rest in ‘others’ category.
Please help how to achieve this.

client name project name revenue cumulative revenue rank
google google 1 100 300 1
google google2 200 300 1
tesla tesla1 100 200 2
tesla tesla2 100 200 2
apple apple1 75 150 3
apple apple2 75 150 3
meta meta1 50 100 4
meta meta2 50 100 4
2

Hello @spitfire !

For the first part, I recommend using runningSum partitioned by your client name.

Then you can try that field within a rank or denseRank field depending on your actual dataset.

After that, you can create an ifelse like that is similar to the one below:

ifelse(
{rankcalc} <=2,   
{client name}, 
{rankcalc} >=3, 
"Other",
NULL
)

Let me know if this helps!

1 Like
3

Calculation for running sum and dense rank worked well but when creating calculation for categorizing clients based on rank I got error.

image

4

Hi @spitfire ,
Will this option to set # of slice displayed for your visual help ?

image
image1174×647 71.3 KB

5

Thank you so much…yeah it worked.
But can you help me fix the mismatched aggregation error that I’m getting if trying to do it the other way. I get this error pretty often in other scenarios as well.

6

Hello @spitfire !

I’m glad that @Neeraj’s comment was able to help.

For the mismatch aggregation error I recommend checking out this blog from another community expert:

1 Like