how to choose the product having maximum sales and expense

[vishus913]

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

[sibin_jacob]

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))

[sunny_talwar]

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?

[mtucholski]

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).

[vishus913]

hi sunny,

yes

i want to do exactly the same thing

and then show that in a pki

[sunny_talwar · Accepted solution]

May be this

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

[vishus913]

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?

[vishus913]

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

[sunny_talwar]

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))

[mtucholski]

Sure,

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