How to classify customers using calculated dimension?

[#1]

Hi guys,

I have a sales table as below:

dateid	custid	salesorder	amt
2011-01-12	A	1	200
2011-04-13	A	2	220
2012-03-23	A	3	100
2012-05-18	A	4	150
2012-03-03	B	1	130
2012-06-08	C	1	80
2009-11-20	D	1	130
2012-09-17	D	2	180

When I select 2012-01-01<=dateid<=2013-12-31, I want get the following table:

Status	No. of Client
New	2
Existing	1
Reactivated	1

Defination:

New:Customers who have first purchase in 2012-01-01<=dateid<=2013-12-31,

Existing:Customer who both purchase in 2012-01-01<=dateid<=2013-12-31 and 2011-01-01<=dateid<=2012-12-31,

Reactivated:Customer who both purchase in 2012-01-01<=dateid<=2013-12-31 and before 2011-01-01.

That means for the 2012-01-01<=dateid<=2013-12-31,

A belongs to 'Existing',

B and C belong to 'New',

D belongs to 'Reactivated'.

Anyone has ideas?

Thanks a lot.

[#2]

Hi, sorry for forget the attached document.

[Gysbert_Wassenaar]

See attached qvw