11 Replies Latest reply: Aug 9, 2018 9:04 AM by Sunny Talwar

# how to choose the product having maximum sales and expense

hi everyone,

i have a table t with

product, sales and expense column, e.g

t:

Product, sales, expense

1          ,  20,          ,30

1          ,  40          ,30

2          ,20           ,25

now i want the name of the product whose expense is highest and is having the maximum sales....

can anyone suggest me a way to do this

thanks

Vikas

• ###### Re: how to choose the product having maximum sales and expense

Product, which is having highest expense in a text object

=FirstSortedValue(Distinct Aggr(Sum(expense),Product))

Product, which is having max sales in a text object

=FirstSortedValue(Distinct Aggr(max(sales),Product))

• ###### Re: how to choose the product having maximum sales and expense

Are you looking to find the product which has the biggest expense and if that ties with another product, you want to then find the one with the highest sales among the one with the biggest expense?

• ###### Re: how to choose the product having maximum sales and expense

hi sunny,

yes

i want to do exactly the same thing

and then show that in a pki

• ###### Re: how to choose the product having maximum sales and expense

May be this

FirstSortedValue(Product, -Aggr(Sum(Expense) + Sum(Sales)/1E10, Product))

• ###### Re: how to choose the product having maximum sales and expense

hi sunny thanks a lot, it's working fine

bt in some cases when if i make selections on year, some then in some years there are products, who have same rank in firstSortedvalue function, in that case it's returning null... is there a way to resolve this issue

regards,

Vikas

• ###### Re: how to choose the product having maximum sales and expense

If there are two with the same rank based on Expense and Sales, which one of the two would you want to see? Any one or both? For Anyone... try this

FirstSortedValue(DISTINCT Product, -Aggr(Sum(Expense) + Sum(Sales)/1E10, Product))

• ###### Re: how to choose the product having maximum sales and expense

in that case i would like to see the both

• ###### Re: how to choose the product having maximum sales and expense

May be this

Concat(DISTINCT {<Product = {"=Rank(Sum(Expense)) < 2 and Rank(Sum({<Product = {[=Rank(Sum(Expense)) < 2]}>}Sales)) < 2"}>} Product, ', ')

• ###### Re: how to choose the product having maximum sales and expense

Hi,

I summed up all sales in load script and then I created a Grid Chart. The top right product is the one you are looking for. I also added number of sales - size of the bubble depends on it.

There is also a straight table with checked max number of records setted to 1 (Presentation tab of the chart).

• ###### Re: how to choose the product having maximum sales and expense

hi mateusz,

i can not do this in  script, because i have other columns also, i have just given a brief here,

can i do that in UI only?

• ###### Re: how to choose the product having maximum sales and expense

Sure,

with calculated dimension for Grid Chart and some changes in Straight table it lokks the same. Check the sample.