Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
BI Consultant
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.
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
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.
BI Consultant
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?
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.
BI Consultant