How to calculate who the top customer is

[#1]

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?

[salto]

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.

[kaushiknsolanki]

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   

[Miguel_Angel_Baeyens · Accepted solution]

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

[#5]

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?

[Miguel_Angel_Baeyens]

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