Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
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