Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
=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?
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)