topic Calculated Dimension: Selecting top 10 by value in QlikView

Calculated Dimension: Selecting top 10 by value

Mon, 17 Dec 2012 13:03:28 GMT

Hello all,

I have a table with customer information and want to use the customer_name as a dimension in a line chart, generating a different line for each customer. Now I don't want all the customers to be in the dimension. Only my Top 10 customers by turnover. I have calculated the turnover per customer in the script so it is added to the customers-table. This gives these fields in the table: Customer_Number, Customer_Name, Customer_Turnover.

It has to be done in the front-end, because the selection of clients can change. If I select only 100 of the 1000 customers, I want the top ten of these 100, not of the 1000 customers.

I have tried as a calculated dimension using following expression: if(Customer_Turnover > FirstSortedValue(Customer_Turnover,Customer_Turnover,10),Customer_Name) but this only gives 'error in calculated dimension'. I have also tried with the Rank()-function but this didn't give me the required solution neither (again an error).

Does anyone have a solution for something like this?

Thanks in advance

Re: Calculated Dimension: Selecting top 10 by value

Miguel_Angel_Baeyens — Mon, 17 Dec 2012 13:16:12 GMT

Hi,

Did you try using the tab "Dimension Limits" in the chart properties? Set it to restrict values showing only the 10 largest, according to the first expression.

If not, you will need an expression like the following:

=If(Aggr(Rank(Aggr(Sum(TurnOver), Customer_Name)), Customer_Name) > 0 AND Aggr(Rank(Aggr(Sum(TurnOver), Customer_Name)), Customer_Name) < 11
, FieldName)

Hope that helps.

Miguel

Re: Calculated Dimension: Selecting top 10 by value

Mon, 17 Dec 2012 13:17:06 GMT

u can also create a input variable for customer and add the variable in Dimension limits to restirct the no of customers........ In input box if u select 10 you'll get a list of 10 customers.....