I have 20 clients, and each client has multiple projects. I only want to show top 5 clients and the revenue generated by them and put rest of the clients in Other category. Then I need to show this in a pie chart.
Please suggest how to solve this?
@spitfire, you can create a dense rank calculated field using customer revenue . Use this rank field to create a new calculated field for clients to show client name as is for top 5 ranked and ‘others’ for any client where rank is greater than 5.
ifelse({rank_field}>5, “Others”,{Client}).
Thanks
it didn’t work, tried your way
I don’t think its syntax issue, ifelse syntax seems fine here.
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.
I guess now it’ll be easy for you to understand the scenario, please help how to achieve this.
| client name | project name | revenue | cumulative revenue | rank |
|---|---|---|---|---|
| google 1 | 100 | 300 | 1 | |
| 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 |
@spitfire much simpler approach, you can create a pie chart with client and revenue columns, by default it will show all the 4 clients from the table above. Then you can update the properties to show number of slices displayed to 2 if you want to show top 2 and include rest in others group as shown below
