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

    firstsortedvalue

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

                  ];

                   

                  Then

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