5 Replies Latest reply: Jun 5, 2013 9:00 PM by Will Wainwright RSS

    FirstSortedValue with string and date

      Hi guys,


      I have a table like the following:



      NameService NoDate



      Now what i want is for any month to find the name accosiated with the service, or the most recent.


      E.g. service 1 on 03/2013, i require it to return xyz


      Which is easy.


      But if no month is selected i require it to return the most recent.


      e.g. Service 2 to return xyz


      I could use maxstring, but i need it ordered by date not alaphabetically.


      I've tried using firstsortedvalue(Name,Date) when it's filtered on a service but still no luck.


      Any ideas?


      Thanks so much in advance.

        • Re: FirstSortedValue with string and date
          Kumar Natarajan



          Try below mentioned script.





          LOAD ServiceNo,Date(Date#(Date,'MMM-YY'),'MMM YYYY') as Date,if(len(Name)=0,Peek(NewName),Name) as NewName;
          LOAD * INLINE [
              Name, ServiceNo, Date
              abc, 1, May-13
              xyz, 2, May-13
              abc, 1, Apr-13
              xyz, 2, Apr-13
              abc, 1, Mar-13
              xyz, 2, Mar-13
              , 1, Feb-13
              xyz, 2, Feb-13
              , 1, Jan-13
              , 2, Jan-13


          Find the attached file

          • Re: FirstSortedValue with string and date
            Stefan Wühl

            FirstSortedValue() should work just fine.


            You need a numeric Date, which I assume that's what you created (using QV date / time functions).


            Then create a table chart with service number as dimension and as expression


            =FirstSortedvalue(Name, -Date)


            [Minus sign is important to get the most recent / highest date, not the lowest]

            • Re: FirstSortedValue with string and date

              Finally fixed it.


              For anyone in future, there were two issues that I hadn't addressed.


              Firstly there were more than one name per Date, so a Distinct in the FirstSortedValue was needed.


              Secondly although i had a Date#(Date) in my initial load i didnt put what format the date was in.


              So my ending results were, in my load statement


              Date#(RIGHT([Date],7),'MM/YYYY') as [Date],


              this was to return the month and year only, I could have achived this with the date format at the top but i bring other dates into my model also.



              Secondly in the expression:

              =FirstSortedValue(DISTINCT mob_ServiceOwner,-[Date])


              Which returned the correct results, (although my qlikview doesn't recognise Distinct and puts the red line underneath to show error)