Using AGGR() in set analysis

[dilara_askarova]

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

[rubenmarin · Accepted solution]

Hi, maybe with:

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

[dilara_askarova]

@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)"}>}

[rubenmarin]

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))

[dilara_askarova]

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

[Brett_Bleess]

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