How to do this? tricky query

ecabanas — Thu, 26 Sep 2013 09:39:07 GMT

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

Re: How to do this? tricky query

manas_bn — Fri, 27 Sep 2013 07:07:43 GMT

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

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:

topic How to do this? tricky query in QlikView

How to do this? tricky query

Re: How to do this? tricky query