Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
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.