topic Re: How to get these two values from a table? in QlikView

How to get these two values from a table?

jblomqvist — Wed, 10 Feb 2016 13:01:43 GMT

Hello

I have the following straight table

The Dimension expression is:

=If(Aggr(Rank(Sum({$<LineSalesAmount=-Null>}LineSalesAmount)),SalesPerson)<=5,SalesPerson)

Expression tab expression is:

Sum(OrderRecordCounter)

From the table created above I would like to get just the name of the sales person with the most orders in a text object and in another text object their orders value.

So in the screenshot above it would be Rob Carsson in text object 1 and the value 1208 in text object two.

Does anyone know how to create an expression for this please? I have tried to the best of my knowledge but cannot figure out how to do it.

Re: How to get these two values from a table?

PradeepReddy — Wed, 10 Feb 2016 13:06:28 GMT

try like this..

concat(If(Aggr(Rank(Sum({$<LineSalesAmount=-Null>}LineSalesAmount)),SalesPerson)<=5,SalesPerson))

Edit:

Above expression will list out all the sales person names in Text box

Re: How to get these two values from a table?

sunny_talwar — Wed, 10 Feb 2016 13:07:42 GMT

Try this in text box:

Name

FirstSortedValue(SalesPerson, -Aggr(Sum({$<LineSalesAmount=-Null>}OrderRecordCounter), SalesPerson))

MaxValue

Max(Aggr(Sum({$<LineSalesAmount=-Null>}OrderRecordCounter), SalesPerson))

Re: How to get these two values from a table?

maxgro — Wed, 10 Feb 2016 13:11:39 GMT

for people with most orders in a textbox I think you can look at

Qlik Design Blog : Displaying the Top Salespers... | Qlik Community

Re: How to get these two values from a table?

Wed, 10 Feb 2016 13:21:47 GMT

First Text object expression: =firstsortedvalue(NAME(of sales person column) , -Aggr(sum([Orders]),NAME))

Second test object exp:= Max(aggr(sum(Orders),NAME))

Hope this will help.

Re: How to get these two values from a table?

hic — Wed, 10 Feb 2016 15:28:57 GMT

... further, your Set Expression is incorrect. {$<LineSalesAmount=-Null>} does not make any sense.

NULLs are automatically excluded when Sum(LineSalesAmount) is used, so you don't need the Set Expression. Should you anyway want to use a Set expression to exclude NULLs, you could use {$<LineSalesAmount={"*"}>} which will select all possible values.

HIC