How to find highest sales of Products on country wise

[Lakshman_1031]

Hi All,

I have product wise sales information Like

Country    Product    Sales

USA            Motors        500

USA            Spare          500

USA           Keys           300

USA            Tyres          200

IND          Soaps         300

IND          Micher      200

UK          Raw Parts    400

UK           Merge Parts   5000

My Out put is;

country   Top Sales product wise     Product Name

USA             500                                               Motors,Spare

IND               300                                              Soaps

UK               5000                                             Merge Parts

I am using firstsortedvalue, but Product name is displaying only One Name and some times it's blank.

Please suggest me how to achieve this scenario, if possible share example.

Thanks & Regards,

Lakshman

[Anonymous]

use this formula (i tried with your data and an inline table and I get your desired result)

dimension: Country

expression: =max(aggr(sum(Sales),Country,Product))

[nevilledhamsiri · Accepted solution]

Apply below chart expression to see what you expect is arrived at?

IF(PRODUCT=FirstSortedValue (TOTAL<COUNTRY>PRODUCT,-AGGR(SUM(SALES),PRODUCT,COUNTRY)),SUM(SALES))

[Saravanan_Desingh · Accepted solution]

One solution is.

tab1:
LOAD RowNo() As RowID, * INLINE [
Country, Product, Sales
USA, Motors, 500
USA, Spare, 500
USA, Keys, 300
USA, Tyres, 200
IND, Soaps, 300
IND, Micher, 200
UK, Raw Parts, 400
UK, Merge Parts, 5000
];

Left Join(tab1)
LOAD Country, FirstSortedValue(Sales, -(Sales+RowID/1E10)) As Max_Sales
Resident tab1
Group By Country;

Left Join(tab1)
LOAD Country, Concat(DISTINCT If(Max_Sales=Sales,Product)) As Products
Resident tab1
Group By Country;

[Saravanan_Desingh]

Output.

[Kushal_Chawda · Accepted solution]

you can use below expressions in your chart

// For Sales
=FirstSortedValue( distinct total <Country> aggr(sum(Sales),Country,Product), -aggr(sum(Sales),Country,Product))

// For top Products
=Concat(aggr(if( sum(Sales) = max(total <Country>aggr(sum(Sales), Country,Product)), Product),Country,Product),',')

[Brett_Bleess]

You have received multiple posts on your question, we would greatly appreciate it if you would return to the thread and close it out by using the Accept as Solution button on the post(s) that helped resolve things to give the poster(s) credit for the help and let the other Members know what worked for your use case.  If you did something different, please consider posting what you did and then mark that as the solution to close the thread.  If you require further assistance, please leave an update post.

Regards,
Brett