Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
ecabanas
Creator II
Creator II

How to do this? tricky query

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

1 Reply
manas_bn
Creator
Creator

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:

Capture.PNG.png