How to use count aggr in backend in Qliksense

[rkpatelqlikview]

Hi Team,

Please suggest how to achieve the below task.

I have data Plant level and Unit wise. the status is based on Unit wise. Now i want show the status on Plants where the status at least one Status ='Y'. Example file attached. 

Example Plant XYZ having 2 units. There status 1C unit is N and status 1D is Y then XYZ Plant status should be 'YES'.

Atleast one status should be Y. 

Thanks in advance.

[Channa]

if(ISNULL(AGGR(ONLY(status),Plant)),'Y','N')

IS IT POSSIBLE TO HAVE MORE THAN ONE 'N' FOR PLANT??

[Channa · Accepted solution]

main:
LOAD
Plant,
units,
status

FROM [lib://com/COMM.xlsx]
(ooxml, embedded labels, table is Sheet1) ;

load Plant,
units,
status, if(status='N',1,2) as St2 Resident main Order by Plant,
units,
status ASC;

drop Table main;

report expression

=IF(max(total <Plant>St2)=2,'Y','N')