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.

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_Time USER_ID Pt_ID 7/1/12 7:52 AM A 1583908 7/1/12 7:52 AM A 1583908 7/1/12 7:52 AM A 1583908 7/1/12 7:52 AM A 1583909 7/1/12 7:52 AM A 1583909 7/1/12 7:52 AM A 1583909 7/1/12 7:53 AM A 1583909 7/2/12 7:52 AM A 1583908 7/2/12 7:52 AM A 1583908 7/2/12 7:52 AM A 1583908 7/2/12 7:52 AM A 1583910 7/2/12 7:52 AM A 1583910 7/2/12 7:52 AM A 1583910 7/2/12 7:53 AM A 1583910

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

• ###### 1. Re: Trying to get an average of events per user per day, over a period of time.

One more piece of information:

I can get to a count distinct of date per user and a count distinct of Pt_Id per user.  Once I get here the next thing I want to do is Sum the count distinct of date and Sum the count distinct of Pt_Id per user.  Divide one by the other and be done.

The problem is that I cannot Sum or Count an aggregation.

• ###### 2. Re: Re: Trying to get an average of events per user per day, over a period of time.

Hey James,

I think the answer here depends on how we will eventually display the values - whether we want to show the average along with showing the distinct count per day (so including the [Date_Time],[UserId] dimensions to the view) or whether you just want to show the average per user for the time frame (so only include UserID to the view).

For the first, using an window_average to create an average of the COUNTD(Pt_ID) across the days shown would work out.  This would just create an average of the sequence of COUNTD(Pt_ID) for the dates lists on the view and can serve as a good means if the number of Pt_ID moves above the ranges average or not during the time frame.

For the later where we are looking for the total average over the time frame, you will want to:

1) Create a Date that removes the timestamps (Since we don't want to aggregate our data differently because the log contains different minute stamps):

• I created this with a simple Date conversion calc that is simple DATE([Date]). The date conversion takes off the timestamps for our purpose
• You can also achieve this by using the "Create Custom Date..." function and designating it to be Month/Day/Year level
• Finally, simply converting the date's data type from Datetime to Date will also work out

2) Create a combined field of our unique dates with the Pt_ID field.  This allows us to countthe same Pt_ID across different dates as separate events or different Pt_ID on the same date as separate events.  A simple concatenation calculation would work: [Pt_ID]+STR([Date without Time Stamp])

3) The final COUNTD([PT_ID+ Date])/COUNTD([Date without Time Stamp]) should now give us the appropriate Average events per day and can be display against each user.

I've attached both methods out.  Hope this helps!

• ###### 3. Re: Trying to get an average of events per user per day, over a period of time.

I need to do some more reading because I do not understand Measure Values, so I was not able to complete your method.

However, the idea to combine Pt_ID with Date gave me a workable way to count unique values per day and I am able to get the answers that I need.  Thank you very much!

• ###### 4. Re: Trying to get an average of events per user per day, over a period of time.

James then please give yourself a correct answer so this moves out of the unanswered questions list. Thanks,

--Shawn

• ###### 5. Re: Trying to get an average of events per user per day, over a period of time.

How do I do that?  Wilson Po has a big green star on his reply which is marked Correct.  What else do I do?

• ###### 6. Re: Trying to get an average of events per user per day, over a period of time.

Beg your pardon. On the front page Jive was still showing this unanswered. Thanks for marking it. Helps us find the unanswered.

Cheers,

--Shawn