Using Sets, FirstSortedValue and Aggr

[#1]

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.

[Oleg_Troyansky]

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

sum(

       AGGR(

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

     , ServiceName)

)

[#3]

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.

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

[#5 · Accepted solution]

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)