3 Replies Latest reply: Dec 30, 2014 12:42 PM by Bill Markham RSS

    Set Analysis Big Challenge

      Hi all,

      I have a new challenge in my qlikview app but I don't know if it will possible to solve it.

      I have data related to customer complaints from my CRM as following example:

      Activity_idActivity_main_idCreated_dateLast_updateStatus
      11001/11/201401/11/2014Registered
      21001/11/201410/11/2014Closed
      51001/11/201430/11/2014Re-Opened
      61001/11/201401/12/2014Closed
      1001001/11/201403/12/2014Re-Opened
      1011001/11/201410/12/2014Closed

       

      My goal is calculate how many complaints have a workflow superior to 72 hours. Now I have calculated all complaints 'closed' that have (Last_update - Created_date) > 72 hours. I have excluded, instead, all activity_main_id that are re-opened because in that case the date difference would be between Last_update(closed) and Last_update(re-opened previous the closed status).

      This is a montly KPI, so i expect :

      • November : 1 complaint because 10/11/2014(activity_id = 2) - 01/11/2014 (date_created) > 72 hh;
      • December: 1 complaint because [01/12/2014(activity_id = 6) - 30/11/2014 (activity_id = 5) < 72 h] but [10/12/2014 (activity_id= 101) - 03/12/2014(activity_id=100) > 72 h]

      So the updated table would be:

      Activity_idActivity_main_idCreated_dateLast_updateStatus
      11001/11/201401/11/2014Registered
      21001/11/201410/11/2014Closed
      51030/11/201430/11/2014Re-Opened
      61030/11/201401/12/2014Closed
      1001003/12/201403/12/2014Re-Opened
      1011003/12/201410/12/2014Closed

       

      In this way I can select only Closed activity_main_id in which Last_update - Created_date > 72 hours.

      Any idea it will be appreciated.

       

      Thank you,

      Romina