Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
TomH
New Contributor

Firstsortedvalue syntax

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. 

 

Orders.png

 
 
 

 

1 Solution

Accepted Solutions
Highlighted

Re: Firstsortedvalue syntax

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

View solution in original post

2 Replies
Highlighted

Re: Firstsortedvalue syntax

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

View solution in original post

Highlighted
TomH
New Contributor

Re: Firstsortedvalue syntax

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.