topic Filtering a dimension using AGGR in QlikView

Filtering a dimension using AGGR

Tue, 14 Feb 2012 11:24:38 GMT

Hi everyone,

I have a straight table showing how many forms per incident have been completed. I would like to filter the results to show where there is more than 1 form per incident. So far I have this expression which shows how many forms for all incidents: -

count({<Master.formType={'*61*','*62v*','*101*','*67*','*140*'}>} Master.counter_master)

I have another expression that shows if there was more than 1, regardless of the number of forms competed: -

IF(count({<Master.formType={'*61*','*62v*','*101*','*67*','*140*'}>} Master.counter_master)>1,1,NULL())

I have tried to put these into a single AGGR expression within the dimension but am not having much luck. The below expression returns '10' for every incident.

=aggr(count(if(Master.formType like '*61*' or '*62v*' or '*101*'or '*67*'or '*140*',1)), Master.counter_master)

Would anyone be able to suggest what I should be doing?

Many thanks

Filtering a dimension using AGGR

CELAMBARASAN — Tue, 14 Feb 2012 11:35:43 GMT

Hi,

You can try with this expression

=aggr(count(if(Master.formType like '*61*' or '*62v*' or '*101*'or '*67*'or '*140*',1)),Master.counter_master,Master.formType)

Celambarasan

Filtering a dimension using AGGR

jagan — Tue, 14 Feb 2012 11:43:38 GMT

Hi,

Try the following expression

=Sum(aggr(Sum(if(Master.formType like '*61*' or '*62v*' or '*101*'or '*67*'or '*140*',1)),Master.counter_master, Master.formType))

Regards,

Jagan.

Filtering a dimension using AGGR

Tue, 14 Feb 2012 11:56:21 GMT

Hi Celambarasan, when I try your formula I have either 0 or 10 as a result.

Hi Jagan - when I try yours I receive error in calculation.

thank you both for taking a look.

Filtering a dimension using AGGR

CELAMBARASAN — Tue, 14 Feb 2012 12:01:37 GMT

Hi,

Can you explain your requirement please?

Celambarasan

Filtering a dimension using AGGR

lironbaram — Tue, 14 Feb 2012 12:01:53 GMT

assuming your dimension is incident

then

aggr( IF(count({<Master.formType={'*61*','*62v*','*101*','*67*','*140*'}>} Master.counter_master)>1,incident),incident)

Re: Filtering a dimension using AGGR

Tue, 14 Feb 2012 12:18:48 GMT

Hi Iiron,

Thanks for the formula, that's exactly what I needed!

It shows the incident number only if there is more then 1 form for the incident so I can then select the suppress null values box in the dimension to show only those incidents with more then 1 PCR.

Thank you very much.