Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

vishus913
New Contributor III

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

1 Solution

Accepted Solutions
MVP
MVP

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

May be this

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

11 Replies
sibin_jacob
Contributor III

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

MVP
MVP

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?

mtucholski
Contributor

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

vishus913
New Contributor III

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

MVP
MVP

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

May be this

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

vishus913
New Contributor III

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?

vishus913
New Contributor III

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

MVP
MVP

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

mtucholski
Contributor

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.