4 Replies Latest reply: Aug 15, 2011 6:35 AM by Alex Nimmo RSS

    Using Sets, FirstSortedValue and Aggr

      Hi, I am trying to count the latest specific grades for a range of ServiceName(s). I have got as far as the following, which gives me the count for the most recent Service for the most recent date, but zeroes for the other ServiceNames, as expected. I have been struggling as to how I should aggr() this for a  range of ServiceName(s):

       

      =(Count({<QAFID={'$(=FirstSortedValue(QAFID,-LAValOrSelfAssessDate))'},AssessmentSupportPlanning={"A"}>}DISTINCT QAFID)+
      Count({<QAFID={'$(=FirstSortedValue(QAFID,-LAValOrSelfAssessDate))'},ClientInvolvementEmpowerment={"A"}>}DISTINCT QAFID)+
      Count({<QAFID={'$(=FirstSortedValue(QAFID,-LAValOrSelfAssessDate))'},Complaints={"A"}>}DISTINCT QAFID)+
      Count({<QAFID={'$(=FirstSortedValue(QAFID,-LAValOrSelfAssessDate))'},FairAccessDiversityInclusion={"A"}>}DISTINCT QAFID)+
      Count({<QAFID={'$(=FirstSortedValue(QAFID,-LAValOrSelfAssessDate))'},NeedsRiskAssessment={"A"}>}DISTINCT QAFID)+
      Count({<QAFID={'$(=FirstSortedValue(QAFID,-LAValOrSelfAssessDate))'},ProtectionFromAbuse={"A"}>}DISTINCT QAFID)+
      Count({<QAFID={'$(=FirstSortedValue(QAFID,-LAValOrSelfAssessDate))'},SafeGuardingProtectionAbuse={"A"}>}DISTINCT QAFID)+
      Count({<QAFID={'$(=FirstSortedValue(QAFID,-LAValOrSelfAssessDate))'},SecurityHealthSafety={"A"}>}DISTINCT QAFID)+
      Count({<QAFID={'$(=FirstSortedValue(QAFID,-LAValOrSelfAssessDate))'},SupportPlanning={"A"}>}DISTINCT QAFID))
      
      


      Thanks in advance for your help.

        • Using Sets, FirstSortedValue and Aggr
          Oleg Troyansky

          looks like you managed to over-complicate your problem How about this:

           

          sum(

                 AGGR(

                  Count({<QAFID={'$(=FirstSortedValue(QAFID,-           LAValOrSelfAssessDate))'}>}DISTINCT QAFID))

               , ServiceName)

          )

            • Using Sets, FirstSortedValue and Aggr

              Thanks for your reply Oleg, but unfortunately that is not what I was after.

               

              To clarify, I am grouping the grades, so as above that should calculate for all the grades marked "A".

               

              The problem I am having is that I need the fragment:

               

              {<QAFID={'$(=FirstSortedValue(QAFID,-LAValOrSelfAssessDate))'},AssessmentSupportPlanning={"A"}>}DISTINCT QAFID

               

              not just to pick up the FirstSortedValue for the entire dataset, but each QAFID for the last LAValOrSelfAssessDate per ServiceName.

                • Using Sets, FirstSortedValue and Aggr
                  Oleg Troyansky

                  Ah, OK, then you need to structure your Set Analysis condition around the date and not around the QAFID. Your verbal definition of the problem determines what field to use in Set Analysis:

                   

                  "I need the last QAFID" - busind a condition based on QAFID.

                   

                  "Ineed all QUAFID records associated with the latest date - build your condition on the Date field:

                   

                  LAValOrSelfAssessDate ={ $(=date(max(LAValOrSelfAssessDate))) }

                   

                  including this into an AGGR by ServiceName will actually ensure that the latest date is selected by ServiceName.

                    • Re: Using Sets, FirstSortedValue and Aggr

                      Thanks for this, it might come in helpful, but I have found a simpler solution that works for me:

                       

                      if(FirstSortedValue(AssessmentSupportPlanning,-LAValOrSelfAssessDate)='A',1,0) +

                       

                      (in a chart per service)

                       

                      Sum(Aggr(if(FirstSortedValue(AssessmentSupportPlanning,-LAValOrSelfAssessDate)='A',1,0),ServiceName))

                       

                      (as a partial of an overall sum)