How to do this? tricky query

[ecabanas]

Hi,

I have a database with the revenues of every customer, I would like to know what I consider a super loyal customer: The customer that has bought us once or more evry month last 3 months.

The Data base has the folling structure:

Salesid     custaccount     date

1               A                    31/06/2013

2               B                    30/07/2013

3               A                    15/07/2013

4               B                    31/08/2013

5               A                    15/09/2013

Superloyal Cutomer: A with 3 sales once as minimum every months last 90 days

*Date= Today

Thanks guys

[manas_bn]

Hi Eduard,

May be this will work?

//Raw Data

TEMP_Fact:

LOAD *                                    // Create Month Field

,MonthName(date) as Month

INLINE [

Salesid,     custaccount,     date

1, A, 30/06/2013

2, B, 30/07/2013

3, A, 15/07/2013

4, B, 31/08/2013

5, A, 15/09/2013

6, B, 24/04/2013

7, A, 30/07/2013

8, C, 23/03/2013

8, C, 23/03/2013

9, C, 25/06/2013

10, C, 23/07/2013

11, C, 23/08/2013

];

//// Calculate No of Months and Sales in last 90 days

LEFT JOIN (TEMP_Fact)

LOAD

custaccount

,count(distinct Month) as NoOfMonths

,count(custaccount) as [Sales (last 90 days)]

RESIDENT TEMP_Fact

//Last 90 days only

Where num(date)>=num(today())-90

group BY custaccount ;

Fact:

NoConcatenate

Load Salesid, custaccount, date, Month,

  // Sales for the last 90 days

  if((num(date)>=num(today())-90),[Sales (last 90 days)],0) as [Sales (last 90 days)],

  if(NoOfMonths>=3,'Super Loyal Customer',null()) as LoyalCustomerFlag

Resident TEMP_Fact;

drop table TEMP_Fact;

Result: