Load top 10 Records based on numeric columns

[#1]

Hi,

I am loading Customers and their amounts from database. I have few reports which are for Country, Industry etc. In one of them I am suppose to display top 10 customer according to their amounts (e.g. Load Taken). Rest of them should be considered as 'Others' in the pivot table. Using pie chart I can do this by setting intervals such that customers with less amount would always be part of 'Others' in the chart.

However I want to display this as table/pivot: Top 10 customer with their amounts. Rest all customers should be shown as Other's and amounts should be added up.

I thought of initially doing it in the script: Using SQL load top 10 customers and consider rest as others. Not sure if would be able to achieve it this way, but thought may be I shall give a try.

Is there any other option to do it - without using SQLs? Anyone faced this before?

[john_duffy]

Hello.

I don't know how to handle this scenario in the chart. I have attached an application that uses Load Order By and the Peek command to create a pivot chart in the format that you want.

Let me know if this helps.

John.

[vgutkovsky · Accepted solution]

This is actually pretty simple to do using the rank() function. Create a calculated dimension that looks something like:

aggr(
 if(rank(total sum(amount))<=10,[Customer Name],'Other'),
 [Customer Name]
)

The expression would be sum(amount) or something similar.

Regards,

[#4]

Thanks Vlad. It worked wonders and I am able to get the expected results.