topic Re: quick question regarding first sorted value in QlikView

quick question regarding first sorted value

Thu, 26 Jul 2012 15:34:56 GMT

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!

Re: quick question regarding first sorted value

swuehl — Thu, 26 Jul 2012 15:47:52 GMT

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

Re: quick question regarding first sorted value

Thu, 26 Jul 2012 16:01:26 GMT

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.

Re: quick question regarding first sorted value

nagaiank — Thu, 26 Jul 2012 16:11:11 GMT

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.

Re: quick question regarding first sorted value

Thu, 26 Jul 2012 16:11:23 GMT

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?

Re: quick question regarding first sorted value

swuehl — Thu, 26 Jul 2012 16:16:52 GMT

In your expression, maybe like this:

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

Re: quick question regarding first sorted value

Thu, 26 Jul 2012 16:21:29 GMT

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

Re: quick question regarding first sorted value

IAMDV — Sat, 04 Aug 2012 17:35:51 GMT

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

Thanks Sam & Stefan.

Cheers,

www.QlikShare.com

Re: quick question regarding first sorted value

Fri, 14 Dec 2012 16:24:22 GMT

Hi..

Please give me reply anyone

Exact use of FIRST SORTED VALUE

just am new to learn....

Re: quick question regarding first sorted value

IAMDV — Mon, 17 Dec 2012 10:24:09 GMT

Hi,

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

http://qlikshare.com/392

Cheers,

Re: quick question regarding first sorted value

Mon, 17 Dec 2012 15:33:05 GMT

HI,

Deepak: thanku for giving me confidence....

Plaease explain Datamodel...

Re: quick question regarding first sorted value

Mon, 17 Dec 2012 16:55:56 GMT

Hi ,

Please any one can Explain.....

Which Schema supports in Qlikview and Why? (Star Schema and SnowFlake Schema)

What is Optimization load ?