Select Sells where first Contact was in a give timeframe

[#1]

Hi,

hope someone can help me with this. I have the following data:

Name	Date	Action
Peter	01.01.2014	First Contact
Peter	01.05.2014	Sell
Max	01.08.2014	First Contact
George	01.04.2014	First Contact
George	01.06.2014	Sell
Jim	01.02.2014	First Contact
Jim	01.10.2014	Sell
Luke	01.02.2014	First Contact
Luke	01.10.2014	Sell

Now I want to count the sells from each person where the first contact was between 01.01.2014 - 28.02.2014.

So it should count 3 times:

Sells where First Contact was between 01.01.2014 - 28.02.2014
Name	Date	Action
Peter	01.05.2014	Sell
Jim	01.10.2014	Sell
Luke	01.10.2014	Sell

Because Peter's first contact was on 01.01.2014.

Jim's first contact was on 01.02.2014.

Luke's first contact was on 01.02.2014.

Can anybody help me with this.

Is it possible with set analysis?

[Gysbert_Wassenaar]

See attached qvw.

[#3]

Hello Gysbert,

thank you for your help.

I tested it, but when I select for example 01.08.2014 - 01.10.2014 it shows me 4 entries, but it should show no one, because only Max has his first contact on 01.08.2014, but has no sell...

[Gysbert_Wassenaar · Accepted solution]

Right you are. A typo. Try this expression:

only({<Action={'Sell'},Name=p({<Action={'First*'},Date={'>=$(vStart)<=$(vEnd)'}>}Name)>}Date)

[#5]

Thank you!! It works!

Can you explain, what does the p stand for in the expression?

[Gysbert_Wassenaar]

P is for possible. It selects those Name values that have a record with Action starting with First and Date between the values of the variable vStart and vEnd.

[#7]

Thank you Gysbert! You helped me so much!!