topic Re: Firstsortedvalue syntax in QlikView

Firstsortedvalue syntax

TomH — Thu, 13 Feb 2020 00:31:53 GMT

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.

Re: Firstsortedvalue syntax

rubenmarin — Thu, 13 Feb 2020 16:44:31 GMT

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

Re: Firstsortedvalue syntax

TomH — Thu, 13 Feb 2020 21:54:59 GMT

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.