Top 5 products by site

[AmirMoha]

Hi all, 

Would you please be able to support me with this?

I am trying to get the top 1 products by sales value by Site. ex;

Product     Site        sales value

product123   UK  10,000
product124   US   9,000
product125  FR   8,000

we can get the top 1 products by sales value, but I want top product by Site, what is the best selling product for each site

[Lisa_P]

Use FirstSortedValue (The minus sign means high to low)

FirstSortedValue(Product, -[sales value])

[sidhiq91]

@AmirMoha  Another solution could be as mentioned below:

See the code below:

NoConcatenate
Temp:
Load * inline [
Product,Site, salesvalue
product123, UK, 10000
product321, UK, 12000
product124, US, 9000
product421, US, 10000
product125, FR, 8000
product521, FR, 7000
];

Exit Script;

In the front end, for the Dimension product use the below expression:

=Aggr(if(Aggr(Rank(Sum(salesvalue)),Site,Product)=1,

Product),Product)

If this resolves the issue, please like and accept it as a solution.

[AmirMoha]

Thank you very much both, but I want to get the product id's in the script side not in the frontend side

both your suggestions worked perfectly in the frontend 

ex a table 

Product     Site        sales value

product123   UK  10,000
product124   US   9,000
product125  FR   8,000

Then in the frontend , I can get a historic sales of product123 because it is the top sales product for UK, to see how was this product performing by date

[PedroNetto]

Temp:
Load * inline [
Product,Site, salesvalue
product123, UK, 10000
product321, UK, 12000
product124, US, 9000
product421, US, 10000
product125, FR, 8000
product521, FR, 7000
];

LOAD Site, Max(salesvalue) as salesvalue Resident Temp
Group by Site;

Left Join

LOAD * Resident Temp;
DROP Table Temp;

Result:

[sidhiq91]

@AmirMoha Please find below  code that can be used in the script .

NoConcatenate
Temp:
Load * inline [
Product,Site, salesvalue
product123, UK, 10000
product321, UK, 12000
product124, US, 9000
product421, US, 10000
product125, FR, 8000
product521, FR, 7000
];

NoConcatenate
Temp1:
Load Site,
Max(salesvalue) as salesvalue
Resident Temp
group by Site;

Left Join
Load * Resident Temp;
Drop table Temp;

Exit Script;

IF this resolves the issue, please like and accept it in the solution.