Firstsortedvalue syntax

[TomH]

Hello all,

My usual goto resource for Qlik stuff is away, so apologies for the really simple question.  I've got a table below where I want to determine the average price for the each product, but also the minimum price.  The table below is only a sample as the actual data is '000 of lines long.

My price is sum(Sales)/Sum(Qty).  This works without issue, and aggregates the data under Product.

FirstSortedValue(distinct(Sales/Qty), SalesValue/Qty) should give me the minimum value, but it seems to be giving me 0, and I don't have any 0 values in my table.  

Any suggestions would be great. 

[rubenmarin · Accepted solution]

Hi Tom, firstsortedvalue will return the dimension text... take a look at help to understand: Help 

Also note that tables by default hides rows with value zero, so possibly there are combinatons with zero but they won't be shown on table.

Solution can be diffferent based on where you want to show the value, in example if you want it on a text object you can use:

Min(Aggr(sum(Sales)/Sum(Qty), [Order No], Product))

[TomH]

Great, thanks for your help.  Still got zero, but you made me think that maybe it's hidden values.  So I did more googling and came up with :

min({<Qty={">0"},Sales={">0"}>} aggr(sum(Sales/Qty), [Order No], [Product])) and hey presto, I am getting the right answer.