topic 3 months repeating customers in App Development

3 months repeating customers

Wed, 17 Aug 2016 16:23:13 GMT

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

Re: 3 months repeating customers

sunny_talwar — Wed, 17 Aug 2016 21:49:27 GMT

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?

Re: 3 months repeating customers

Wed, 17 Aug 2016 21:52:59 GMT

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.