5 Replies Latest reply: Feb 2, 2015 9:48 PM by Paul Walker RSS

    FirstSortedValue. Group By. Any way to do all this in one expression?

      Load Customer,

      firstsortedvalue(PurchasedArticle, OrderDate)as

      FirstProductBought

      from abc.csv group by Customer;

       

      This is the example for FirstSortedValue in the QV reference manual. It groups by the data in the script!

       

      Anyone know a way to use the FirstSortedValue function AND 'Group By' clause within the SAME expression of a chart?

      I don't want to 'group by' in the script because i want a few expressions, each grouping by a different fieldname.

        • FirstSortedValue. Group By. Any way to do all this in one expression?
          Stefan Wühl

          Either use your 'group by' field as dimension in your chart, or as dimension to an advanced aggregation aggr().

           

          Could you explain your setting and requirement a bit more?

            • FirstSortedValue. Group By. Any way to do all this in one expression?

              Yea,

               

              I wish to count the number of ID's, based on the Date and Status. I only want to consider the latest Date for each ID and count how many are good, and how many are bad.

               

              Sample table:

              ID,     ProductType,  AuditorName,      Date,  Status    

              -----------------------------------------------------------------------------------------------

              1         AA            CompanyX        1/1     Good

              1         BB            CompanyX        1/31    Bad

               

              2         BB            CompanyY        1/3     Good

               

              3         CC            CompanyX        1/5     Bad

              3         CC            CompanyX        2/1     Good

              3         AA            CompanyX        3/1     Good

              3         AA            CompanyX        3/2     Bad

               

               

              In my chart, I want the dimension to be either ProductType or AuditorName (I will use two separte charts, or just put these two fields in a cyclic group to use as the dimension)

               

              I need two expressions:

              • Count how many ID's were good,
              • count how many ID's were bad.

               

               

              ********************ProductType*************************

              So if my dimension was Product Type, I wanna see the ID counts for each status, broken down by Product (disregarding AuditorName):

              Product AA, ID 1 ==> Good (1/1)

                                 ID 3 ==> Bad  (3/2)

              Product BB, ID 1 ==> Bad (1/31)

                                 ID 2 ==> Good (1/3)

              Product CC, ID 3 ==> Good (2/1)

               

              Therefore, these would be the counts for each Product TYpe:

                       Status=Good       STatus=Bad

              AA            1                   1

              BB            1                   1

              CC            1                   0

               

               

              ********************AuditorName*************************

              If I choose AuditorName as my dimension, I would count the numbers of ID's in each status, broken down by AuditorName (disregarding Product Type):

              CompanyX, ID 1 ==> Bad (1/31)

                                ID 3 ==> Bad (3/2)

              CompanyY, ID 2 ==> Good (1/3)

               

               

              The resulting count for each AuditorName:

                               STatus=Good    Status=Bad

              CompanyX          0             2

              CompanyY          1             0

               

               

              ================================

              What can I do? THis does not do the job when counting number of Good ID's:

               

              count

              (  

                  { <Date= {"=FirstSortedValue( Date, -Aggr(ProductType, ID) )"} > }

                  distinct if(Status='Good',ID)

              )