Aggregation for more than 5 entries

[ShubhamD_QS]

Hi all, 
I have below sample data, I want Expected status as a output. Only for those where Status is zero for more than 5 times for the same ID (In this case, MyID=2)
 

ID	MyID	Status	Expected Status
A1	1	0	Effective
A2	1	0	Effective
A3	1	0	Effective
B1	2	0	Not Effective
B2	2	0	Not Effective
B3	2	0	Not Effective
B4	2	0	Not Effective
B5	2	0	Not Effective
B6	2	0	Not Effective

Please help me with the expression, I have tried multiple things with AGGR() but not working.

[MatheusC]

Hi, @ShubhamD_QS 

test like this

=aggr(if(Count({<MyID = {"=Count({<Status = {0}>} MyID) > 5"}>}MyID)=1,MyID),ID,MyID)

disable null values ​​in fields.

- Regards, Matheus

[ShubhamD_QS]

Hi @MatheusC 
Thanks for the reply, but I am looking for expected status column as a output.

You can refer below question, I have posted with proper details.
https://community.qlik.com/t5/New-to-Qlik-Analytics/Set-status-if-entries-are-available-more-than-5-...

Thanks once again.
Shubham D

[martinpohl]

Create a table, MyID as dimension and

concat({<MyID={"=count({<Status={0}>}Status)>=5"}>} distinct [Expected Status],',')

as expression

Regards

[diegozecchini]

Hi you can use this:

IF(
AGGR(COUNT({<Status={0}>} Status), MyID) > 5,
'Not Effective',
'Effective'
)