Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
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;
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),',')
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))
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))
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;
Output.
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),',')
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