2 Replies Latest reply: Aug 17, 2016 5:52 PM by Azmina Virani RSS

    3 months repeating customers

    Azmina Virani

      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