Filtering a dimension using AGGR

[#1]

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

[CELAMBARASAN]

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

[jagan]

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.

[#4]

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.

[CELAMBARASAN]

Hi,

     Can you explain your requirement please?

Celambarasan

[lironbaram]

assuming your dimension is incident

then

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

[#7 · Accepted solution]

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.