Count based on several lines with 1 field in common

[#1]

Hi, I have a database like the one below, and i would like to count in a graph something like the expression below:

ID	Activity Action	Activity	Date	Responsable	Rework Type
1	Executed	AA	01/01/2015	A	-
1	Verified	AA	02/01/2015	B	-
1	Rework	AA	03/01/2015	C	RTA
1	Executed	BB	04/01/2015	D	-
2	Executed	AA	04/01/2015	A	-
2	Verified	AA	05/01/2015	B	-
2	Rework	AA	06/01/2015	C	RTB
2	Executed	BB	07/01/2015	D	-

For the same ID,

IF Responsible = "A" for Activity Action "Executed", and Activity Action "Verified" has data in Date Field,  and Activity Action "Rework" has Rework Type = "RTA" , Count 1

I am trying to do this with Set Analisys, but i am starting now to understand how it works.

BR//Marcelo Fonseca

[ramoncova06 · Accepted solution]

I would suggest using a crosstable to activity action type in a regular dimension so you could join the data and create a single entry by row, this is how to do it at the front end

count({

<ID=P({<[Activity Action] = {'Executed'}>}),Responsable = {'A'}>

*

<ID=P({<[Activity Action] = {'Verified'}, ID = {"=nullcount(Date)=0"}>}) >

*

<ID=P({<[Activity Action] = {'Rework'}, [Rework Type] = {'RTA'} >} ) >

}ID)

[#3]

Hi Ramon, I could not get the results. Does the second line should't be like this?

<ID=P({<[Activity Action] = {'Executed'}>}),Responsable = {'A'}>})>

[ramoncova06]

no, the why I added it should work fine see attached example

[#5]

Thats right Ramon, i got it here! But one thing didnot work like i Expected. I need to filter the field "Responsible" for example, if choose A, it counts 1. If i Choose any other, it counts 0. But when i choose A it counts 0 either way.