3 Replies Latest reply: Oct 8, 2013 6:39 AM by Stefan Wühl RSS




      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?




        • Re: firstsortedvalue
          Stefan Wühl

          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

              =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
                  Stefan Wühl

                  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




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