topic Using Sets, FirstSortedValue and Aggr in QlikView

Using Sets, FirstSortedValue and Aggr

Thu, 11 Aug 2011 16:21:47 GMT

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 — Thu, 11 Aug 2011 18:42:00 GMT

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

Fri, 12 Aug 2011 09:29:28 GMT

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 — Fri, 12 Aug 2011 16:02:10 GMT

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

Mon, 15 Aug 2011 10:35:35 GMT

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)