topic Load top 10 Records based on numeric columns in QlikView

Load top 10 Records based on numeric columns

Wed, 08 Sep 2010 11:56:44 GMT

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?

Load top 10 Records based on numeric columns

john_duffy — Wed, 08 Sep 2010 20:26:46 GMT

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.

Load top 10 Records based on numeric columns

vgutkovsky — Wed, 08 Sep 2010 20:51:43 GMT

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,

Load top 10 Records based on numeric columns

Thu, 09 Sep 2010 09:08:54 GMT

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