5 Replies Latest reply: Jun 28, 2011 7:35 AM by Miguel Angel Baeyens de Arce

# How to calculate who the top customer is

Hi

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

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

Hi,

Create a Straight Table.

Give Customer as Dimension.

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

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

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

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