topic Re: How can you aggregate results from an IF statement? in QlikView

How can you aggregate results from an IF statement?

Mon, 08 Sep 2014 17:31:15 GMT

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

Re: How can you aggregate results from an IF statement?

vgutkovsky — Mon, 08 Sep 2014 17:41:49 GMT

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

Re: How can you aggregate results from an IF statement?

Mon, 08 Sep 2014 17:52:57 GMT

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

Re: How can you aggregate results from an IF statement?

vgutkovsky — Mon, 08 Sep 2014 18:10:10 GMT

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)

),ID_NUMBER,ACTIVITY_CODE))

Vlad

Re: How can you aggregate results from an IF statement?

Mon, 08 Sep 2014 19:20:04 GMT

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)

),ID_NUMBER,ACTIVITY_CODE))

Thanks again!

Grace