topic How to calculate who the top customer is in QlikView

How to calculate who the top customer is

Tue, 28 Jun 2011 06:41:49 GMT

I would like to add column in my table to display what customer is the biggest buyer for a product. And in the next column I would like to show how many that customer bought from that product.

Pseudo code for customer name: Print Name where max(sum(InvQty))

Pseudo code for qty: Print qty where max(sum(InvQty))

Any ideas?

How to calculate who the top customer is

salto — Tue, 28 Jun 2011 06:52:17 GMT

Hello,

I would try with a Chart.

One dimension: Customer, and two expressions:sum(SalesAmount) and sum(Quantity). You can sort the chart in descending order by the first expression.

I would use a Listbox with the Product list to select the desired product(s).

Regards.

How to calculate who the top customer is

kaushiknsolanki — Tue, 28 Jun 2011 06:56:05 GMT

Hi,

Create a Straight Table.

Give Customer as Dimension.

Add expression as Sum(InvQty)

Go to Sort Give sort oder as Sum(InvQty) -> select Numeric Value -> Descending for Top Customers and then at the bottom keep Customer.

Regards,

Kaushik Solanki

Re: How to calculate who the top customer is

Miguel_Angel_Baeyens — Tue, 28 Jun 2011 08:42:47 GMT

Hi,

Assuming the following script:

Data:
LOAD Repeat(Chr(64 + Ceil(Rand() * 3)), 3) AS ProdID,
     Repeat(Chr(64 + Ceil(Rand() * 3)), 5) AS CustomerID,
     Ceil(Rand() * 1000) AS Qty
AUTOGENERATE 5; // 5 records so its easier to check whether the results return as expected but you can increase this value

to generate some dummy data, create a new straight table chart, set ProdID as dimension, and add a calculated dimension as follows

=If(Aggr(Rank(Sum(Qty)), ProdID, CustomerID) = 1, CustomerID)

This will return the top buyer for each value in ProdID

Now use this expression to get how much the top buyer has bought

If(Aggr(Rank(Sum(Qty)), ProdID, CustomerID) = 1, Sum(Qty))

Hope that makes sense.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

How to calculate who the top customer is

Tue, 28 Jun 2011 11:29:49 GMT

Hi, Miguel.

That works! Thanks.

But how do I change the heading of that calculated dimension? I get the formula as column heading. Or perhaps I can hide that column?

Re: How to calculate who the top customer is

Miguel_Angel_Baeyens — Tue, 28 Jun 2011 11:35:33 GMT

Hello Andy,

You can set whatever label you want for both dimensions and expressions. Go to the chart properties, Dimensions tab click on the calculated dimension and change the Label, and for expressions do likewise in the Expressions tab. In the listbox, go to the properties and change the Title.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica