topic Re: firstsortedvalue in QlikView

firstsortedvalue

Tue, 08 Oct 2013 10:11:56 GMT

Hi,

Can someone please help me with an expression I have in a bar chart.

=firstsortedvalue( {$<SOLD_DATE = > - <SOLD_DATE = {'*'}>} (Balance - OBJECT_VALUATION),-FlagVintage)

for the moment I'm selecting last record order by FlagVintage desc, but I need the previous record. Tried this, but it didn't work.

=firstsortedvalue( {$<SOLD_DATE = > - <SOLD_DATE = {'*'}>} (Balance - OBJECT_VALUATION),-FlagVintage, 2)

Does someone know the syntax for this?

Br,

Johan

Re: firstsortedvalue

swuehl — Tue, 08 Oct 2013 10:18:55 GMT

What do you mean with 'it didn't work? No returned value or wrong returned value?

Have you checked that you only have a unique value to return for the same second largest FlagVintage?

From the HELP:

...If more than one value of expression share the same lowest sort-order, the function will return null....

Try with a DISTINCT qualifier to see if you get a result back.

Re: firstsortedvalue

Tue, 08 Oct 2013 10:27:41 GMT

=firstsortedvalue( {$<SOLD_DATE = > - <SOLD_DATE = {'*'}>} (Balance - OBJECT_VALUATION),-FlagVintage, 2)

contractnr FlagVintage Sold_Date Balance OBJECT_VALUATION

101 1 null 1000 50

101 2 null 750 50

101 3 null - 50

Maybe I'm using the wrong expression. This is a sample of my table. I would like to get where Sold_Date is null, then take the record before latest record, in this case FlagVintage = 2.

Hope this displays what I'm trying to do?

Re: firstsortedvalue

swuehl — Tue, 08 Oct 2013 10:38:59 GMT

This will also return nothing for the maximum sort weight.

I believe the problem is the way you want to select NULLs, which I think you can't do like this.

It would be easiest if you flag the NULLs in the script:

LOAD *, if(isnull([Sold_Date]),1,0) as IsNullSoldDate;

LOAD contractnr, FlagVintage, NULL() as [Sold_Date], Balance, OBJECT_VALUATION INLINE [

contractnr, FlagVintage, Balance, OBJECT_VALUATION

101, 1, 1000, 50

101, 2, 750 , 50

101, 3, - , 50

];

Then

=firstsortedvalue( {$<IsNullSoldDate = {1}>} (Balance - OBJECT_VALUATION),-FlagVintage,2)