topic Re: Using AGGR() in set analysis in QlikView

Using AGGR() in set analysis

dilara_askarova — Sat, 16 Nov 2024 00:07:48 GMT

Hello, all

I'm stuck with one of my charts, and any help would be appreciated.

I have data that is presented in the chart below, this is a straight table from QV. I'm working on the expression to display MemberCount. MemberCount = The latest member ShortAnswer, in this case I want to see '1' next to 'Action' because it is the latest answer for the member.

My current expression is:

=count(distinct{<SurveyEndDateTimeStageOfChange={"=aggr( max(SurveyEndDateTimeStageOfChange), CBC_MemberID_StageOfChange)"}
>}MemberSurveyIDStageOfChange)

CBC_MemberID_StageOfChange	SurveyEndDateTimeStageOfChange	ShortAnswer	MaxDate	MemberCount
			02/25/2020	1
ASR7730217968E	04/24/2019	Maintenance	02/25/2020	0
ASR7730217968E	02/25/2020	Action	02/25/2020	0
ASR7730217968E	09/17/2019	Maintenance	02/25/2020	1

Re: Using AGGR() in set analysis

rubenmarin — Tue, 18 Aug 2020 10:08:37 GMT

Hi, maybe with:

Sum(Aggr( If(SurveyEndDateTimeStageOfChange=Max(TOTAL <CBC_MemberID_StageOfChange> SurveyEndDateTimeStageOfChange), 1, 0) , CBC_MemberID_StageOfChange,SurveyEndDateTimeStageOfChange))

Re: Using AGGR() in set analysis

dilara_askarova — Tue, 18 Aug 2020 11:47:29 GMT

@rubenmarin, thank you. This might work, is there a way to add following set analysis to maximum date selction?

{<SurveyEndDateTimeStageOfChange ={">$(#vCdmThroughDate12MonthAgo)<$(#vCdmThroughDatePlusOneDay)"},[CDM Lives Only As Of]={"$(=vCdmThroughDate)"}>}

Re: Using AGGR() in set analysis

rubenmarin — Tue, 18 Aug 2020 15:58:47 GMT

Hi, maybe this:

Sum(Aggr( If(SurveyEndDateTimeStageOfChange=Max(TOTAL <CBC_MemberID_StageOfChange> {<SurveyEndDateTimeStageOfChange ={">$(#vCdmThroughDate12MonthAgo)<$(#vCdmThroughDatePlusOneDay)"},[CDM Lives Only As Of]={"$(=vCdmThroughDate)"}>} SurveyEndDateTimeStageOfChange), 1, 0) , CBC_MemberID_StageOfChange,SurveyEndDateTimeStageOfChange))

If doesn't works try with:

Sum({<SurveyEndDateTimeStageOfChange ={">$(#vCdmThroughDate12MonthAgo)<$(#vCdmThroughDatePlusOneDay)"},[CDM Lives Only As Of]={"$(=vCdmThroughDate)"}>} Aggr( If(SurveyEndDateTimeStageOfChange=Max(TOTAL <CBC_MemberID_StageOfChange> {<SurveyEndDateTimeStageOfChange ={">$(#vCdmThroughDate12MonthAgo)<$(#vCdmThroughDatePlusOneDay)"},[CDM Lives Only As Of]={"$(=vCdmThroughDate)"}>} SurveyEndDateTimeStageOfChange), 1, 0) , CBC_MemberID_StageOfChange,SurveyEndDateTimeStageOfChange))

Re: Using AGGR() in set analysis

dilara_askarova — Wed, 19 Aug 2020 14:42:19 GMT

Thank you, @rubenmarin , you saved my day. Glad we have such a great Qlik community.

Re: Using AGGR() in set analysis

Brett_Bleess — Thu, 20 Aug 2020 16:02:43 GMT

Just adding a Design Blog post that may be of some further guidance to you in the future!

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

Regards,
Brett