topic Aggregation: finding the biggest customer for each product in QlikView

Aggregation: finding the biggest customer for each product

Wed, 21 Mar 2012 18:32:39 GMT

I have a table with invoices for a full year. Each invoice specifies the Customer, the Product and the Volume sold.

I am trying to make the following table:

Product
Total volume sold of the product
Name of the largest customer (in terms of volume purchased across all products, not just the volume of the product itself) buying the product
Total volume purchased

Obviously, the first two are trivial, but I am struggeling with the expressions for the last 2 columns.

Have spend hours on this, so help would be very much appreciated!!!

Aggregation: finding the biggest customer for each product

Wed, 21 Mar 2012 19:23:40 GMT

I believe that the customer will be like this using product as dimension

=Aggr(if(Rank(Sum({<Client= P(Client), Product=>}SubTotal)) = 1, Client), Client)

Aggregation: finding the biggest customer for each product

Thu, 22 Mar 2012 10:25:36 GMT

Federico,

Thanks for the suggestion. This helps a lot, but I don't have the final solution yet.

Using your suggestion, I have been able to make a table chart with dimension Customer. When I select a product, this table gives me the ranking of the customers from large to small and also the volume of the customers across all products (not just the product selected). See below:

Now, I want to take the next step. Rather than having the above table with dimension Customer and having to select products to find the largest customer, I would like to make a chart with dimension Product, which shows for each product the biggest customer and the Customer's volume across all products. I have tried your aggregation formula, but to get anything displayed, I need to aggregate across both products and customers. When I do this, I get the customer buying the biggest volume of the specific product rather tha the biggest overall customer who buys the particular product.

Any suggestions?

Thanks, Marc.

Aggregation: finding the biggest customer for each product

Thu, 22 Mar 2012 17:41:48 GMT

The previous formula only worked for the 1st ranked product-client, didnt do the job...

Here is a formula for a table with product as dimension.

Aggr(if(Rank(Sum({<Product=>}total <Client> SubTotal)) = 1, Client), Product, Client)

This shows the the biggest client thats buys each product.

In a minute the volume formula...

Aggregation: finding the biggest customer for each product

Thu, 22 Mar 2012 17:45:01 GMT

Aggr(if(Rank(Sum({<Product=>}total <Client> SubTotal)) = 1, Sum({<Product=>}total <Client> SubTotal)), Product, Client)

Aggregation: finding the biggest customer for each product

Thu, 22 Mar 2012 21:23:28 GMT

Federico,

Fantastic. Works perfect. Thanks a lot.

Marc.

Aggregation: finding the biggest customer for each product

Anonymous — Fri, 23 Mar 2012 09:11:01 GMT

Hi Federico,

don't understand wath is SubTotal in your formula ...

can u explain me please?

Thank

Aggregation: finding the biggest customer for each product

Fri, 23 Mar 2012 15:33:07 GMT

SubTotal is whatever he is using, could be Qty, sales, anything. In this case I believe it should be volume.