How can you aggregate results from an IF statement?

[#1]

Hi ,

I have a slightly complicated IFstatement that allows me to flag  records of individuals who have participated in an any activity 3 years prior to participating in a specific activity.

= If(Max({$<ACTIVITY_CODE = ,

  PARTICIPATION = { 'Attended/Participated'}>}

  START_DATE) < Min( {$<ACTIVITY_CODE = {'HASBS' },

  PARTICIPATION ={'Attended/Participated'}

>} Total  <ID_NUMBER> START_DATE)

AND

Max({$<ACTIVITY_CODE = ,

  PARTICIPATION = { 'Attended/Participated'}>}

  START_DATE) > AddYears(Min( {$<ACTIVITY_CODE = {'HASBS' },

  PARTICIPATION ={'Attended/Participated'}

>} Total  <ID_NUMBER> START_DATE), -3)

, 1, 0)

This works and returns accurate 1s and 0s for whether they participated in activities or not.  Now, I just want to SUM those 1s and 0s to tally up the number of activities individuals participated in.  I've tried the statement below, but return an error.  Can anyone tell me the correct way to aggregate the results of an IF statment?

=Sum( Total <ID_NUMBER>   If(Max({$<ACTIVITY_CODE = ,

  PARTICIPATION = { 'Attended/Participated'}>}

  START_DATE) < Min( {$<ACTIVITY_CODE = {'HASBS' },

  PARTICIPATION ={'Attended/Participated'}

>} Total  <ID_NUMBER> START_DATE)

AND

Max({$<ACTIVITY_CODE = ,

  PARTICIPATION = { 'Attended/Participated'}>}

  START_DATE) > AddYears(Min( {$<ACTIVITY_CODE = {'HASBS' },

  PARTICIPATION ={'Attended/Participated'}

>} Total  <ID_NUMBER> START_DATE), -3)

, 1, 0))

Thanks!

Grace

[vgutkovsky]

Well, the technical answer to your question is aggr() AKA advanced aggregation. But there probably a more elegant way of doing what you need. What are the names of your chart dimensions? And what type of chart is it?

Vlad

[#3]

My Dimensions are Id_Number and Activity_Code.  I've been experimenting with AGGR, but I know I'v got myself too tangled and I'm not going about this the best way. 

Thanks,

Grace

[vgutkovsky · Accepted solution]

No worries, that's what the forums are for 🙂 In your case (because of your chart dims) you'll actually need an aggr unfortunately. Give this a try:

sum(aggr(if(

    max({<ACTIVITY_CODE,PARTICIPATION={'Attended/Participated'}>} START_DATE) < min({<ACTIVITY_CODE={'HASBS'},PARTICIPATION={'Attended/Participated'}>} total <ID_NUMBER> START_DATE)

    and

    max({<ACTIVITY_CODE,PARTICIPATION={'Attended/Participated'}>} START_DATE) > addyears(min({<ACTIVITY_CODE={'HASBS'},PARTICIPATION={'Attended/Participated'}>} total <ID_NUMBER> START_DATE),-3)

        ,1

        ,0

),ID_NUMBER,ACTIVITY_CODE))

Vlad

[#5]

Thanks!  This was so helpful!  To get what I needed, I actually ended up having to ignore the Activity_Code dimension in the final sum, so my logic was

sum( total <id_number> aggr(if(

    max({<ACTIVITY_CODE,PARTICIPATION={'Attended/Participated'}>} START_DATE) < min({<ACTIVITY_CODE={'HASBS'},PARTICIPATION={'Attended/Participated'}>} total <ID_NUMBER> START_DATE)

    and

    max({<ACTIVITY_CODE,PARTICIPATION={'Attended/Participated'}>} START_DATE) > addyears(min({<ACTIVITY_CODE={'HASBS'},PARTICIPATION={'Attended/Participated'}>} total <ID_NUMBER> START_DATE),-3)

        ,1

        ,0

),ID_NUMBER,ACTIVITY_CODE))

Thanks again!

Grace