Counting records with conditions across different rows

[nam]

Hi Guys i have the attached scenario and i need a quick solution, i have given the excel example in the attached.

I have a group of records. 1 record has multiple lines identifing the types so the record ID repeats for every Type and Oper see attached.

i want to count the reordID which contains type of F and Oper=0010 which is straight forward

i then want to count the records ID that contains a line where Type =F and Oper=0010 and also contains type =X on any line where Oper>0010.

Any replies/samples would be appreciated

[vivientexier]

You just need to add more operators to your condition : "or" (with brackets) and "and".

If( (Type = 'F' and Oper = '0010') or (Type = 'X' and Oper > '0010'), 1)

[Gysbert_Wassenaar]

Try: count({<Oper={'0010'},Type={'F'},Service_Order=p({<Oper-={'0100'},Type={'X'}>}Service_Order)>}Type)

[nam]

Thankyou Guys.

Because of large volumes, my solution was create a resident load with a flag for the first part of the logic and join it back on the original table and use set analysis to work out the 2nd Count. its now working fine.