Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

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?

1 Solution

Accepted Solutions
MVP
MVP

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

5 Replies
salto
Valued Contributor II

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.

    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   

MVP
MVP

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

Highlighted
Not applicable

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?

MVP
MVP

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

Community Browser