Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

3 months repeating customers

Can some please help me do this in qlik, in a simpler form..

I have this code which basically checks if a customer did a transaction for 3 straight month..

I just need a count of 3 months recurring customers.

Here is the sql code for it..

SELECT

C.CUSTOMER,

CASE WHEN D.CUSTOMER IS NOT NULL AND E.CUSTOMER IS NOT NULL THEN 'YES' ELSE 'NO' END REPEAT_CUSTOMER

FROM

(

SELECT

DISTINCT CUSTOMER

FROM

NOTIFICATION A,

NOTIFICATION_TYPE B

WHERE A.NOTIFICATION_TYPE_ID = B.ID

AND TRUNC(INSERT_DATE) >= '5/1/2016'

AND TRUNC(INSERT_DATE) < '5/31/2016'

) C

,

(

SELECT

DISTINCT CUSTOMER

FROM

NOTIFICATION A,

NOTIFICATION_TYPE B

WHERE A.NOTIFICATION_TYPE_ID = B.ID

AND TRUNC(INSERT_DATE) >= '4/1/2016'

AND TRUNC(INSERT_DATE) < '4/30/2016'

) D

,

(

SELECT

DISTINCT CUSTOMER

FROM

NOTIFICATION A,

NOTIFICATION_TYPE B

WHERE A.NOTIFICATION_TYPE_ID = B.ID

AND TRUNC(INSERT_DATE) >= '3/1/2016'

AND TRUNC(INSERT_DATE) < '3/31/2016'

) E

WHERE C.CUSTOMER = D.CUSTOMER

AND C.CUSTOMER = E.CUSTOMER

ORDER BY 2 DESC

2 Replies

Re: 3 months repeating customers

Not sure what the requirement is? Are you looking to create a QlikView script that would allow to do the count on the front end of the application? Can you may be elaborate a little on the need?

Not applicable

Re: 3 months repeating customers

I want to use the Customer field and the Insert_Date field in the front end to calculate the number of recurring customer for 3 months. I do not want to use the code i provided in the back end.

i want to

LOAD Customer , Insert_Date

FROM Table;

and be able to calculate on front end the last 30 days recurring customers.