quick question regarding first sorted value

[#1]

Hi Guys,

I have a text object with the following code:

='Division with the highest expenses :'  &  firstsortedvalue(Division,-aggr(sum(expenses),Division))

I would like to display the division name that has the highest expenses. There must be something wrong with my expression.

Hope some one can help.

Cheers

Carter!

[swuehl]

What do you get back? I can't see anything wrong at the moment. Have you compared your result with a straight table with dimension Division and expression sum(expenses)?

Do you have a unique Division with the highest expenses or do more than one Division share the highest expenses?

Have you checked the usual suspects (typo / case sensitivity in field names)?

[#3]

That's the strange part. I only get the text string: 'Division with the highest expenses'. the rest is not displayed.

I double checked the fieldnames, all ok

I created a straight table with the division names, and did a sum(expenses). There is only 1 division that has the highest expenses.

could it be a because im using qlikview 9.6 ? I doubt it, but could be...

I'm going to create some fake data and see if I can upload it.

[nagaiank]

The function FirstSortedValue() returns null if there are more than one value for the sort order specified. This is a limitation in using function. Please check if this is causing the problem in your case.

[#5]

it seems to working fine on another table. I just copied a pasted the expression and changed the field names.

I think I found the cause, it can be that the highest  expenses does not have a division. so I need to do something with the script, like 'supress value when null'

but how can I do that in a text object? or in the script?

[swuehl · Accepted solution]

In your expression, maybe like this:

='Division with the highest expenses :'  &  firstsortedvalue({<Division={"*"}>} Division,-aggr(sum(Expenses),Division))

[#7]

Thanks good solution!

I adjusted the field division in the load script to this:

if(isnull(Division), 'Unkown', Division) AS Division

Then I excluded unknown in my expression.

But your solution is much better

Many Thanks!!!!

[IAMDV]

Stefan - That's very clever! I love this approach. I have learnt something new.

Thanks Sam & Stefan.

Cheers,

DV

www.QlikShare.com

[#9]

Hi..

Please give me reply anyone

Exact use of FIRST SORTED VALUE

just am new to learn....

[IAMDV]

Hi,

I've made two part tutorial on FirstSortedValue(). Please check the below link...

http://qlikshare.com/392

Cheers,

DV