firstsortedvalue

[#1]

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

[swuehl]

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.

[#3]

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

[swuehl]

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)