6 Replies Latest reply on Nov 13, 2013 3:12 PM by Shawn Wallwork

    Trying to get an average of events per user per day, over a period of time.

    James Keuning

      I do not even know how to express this question in words, but I am going to try.

       

      I have a log which records User_ID, Date, and Pt_ID.  A user can log multiple instances of the same Pt_ID on a given day.  A user can also log multiple Pt_IDs on a given day.  What I need to do it figure out how many, on average, unique Pt_IDs a user logged for a specified time.

       

      For example, during a 30 day period (7/1/2012 - 7/30/2012), I might log the following for a user:

       

      Date_TimeUSER_IDPt_ID
      7/1/12 7:52 AMA1583908
      7/1/12 7:52 AMA1583908
      7/1/12 7:52 AMA1583908
      7/1/12 7:52 AMA1583909
      7/1/12 7:52 AMA1583909
      7/1/12 7:52 AMA1583909
      7/1/12 7:53 AMA1583909
      7/2/12 7:52 AMA1583908
      7/2/12 7:52 AMA1583908
      7/2/12 7:52 AMA1583908
      7/2/12 7:52 AMA1583910
      7/2/12 7:52 AMA1583910
      7/2/12 7:52 AMA1583910
      7/2/12 7:53 AMA1583910

       

      The answer I need is 2.  (The user logged two Pt_IDs on 7/1 and then two again on 7/2 - AN AVERAGE OF TWO PER DAY.)

       

      I am trying a combination of COUNTD functions but I am not getting there.  I need to ignore days with null values.

       

      Message was edited by: James Keuning