Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
dilara_askarova
Contributor II
Contributor II

Using AGGR() in set analysis

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_StageOfChangeSurveyEndDateTimeStageOfChangeShortAnswerMaxDateMemberCount

   02/25/20201
ASR7730217968E04/24/2019Maintenance02/25/20200
ASR7730217968E02/25/2020Action02/25/20200
ASR7730217968E09/17/2019Maintenance02/25/20201
Labels (3)
1 Solution

Accepted Solutions
rubenmarin

Hi, maybe with:

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

 

View solution in original post

5 Replies
rubenmarin

Hi, maybe with:

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

 

View solution in original post

dilara_askarova
Contributor II
Contributor II

@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
Contributor II
Contributor II

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

Brett_Bleess
Support (Former)
Support (Former)

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.