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.
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!
Average Distinct Events.twbx 21.7 KB
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!
James then please give yourself a correct answer so this moves out of the unanswered questions list. Thanks,
How do I do that? Wilson Po has a big green star on his reply which is marked Correct. What else do I do?
Beg your pardon. On the front page Jive was still showing this unanswered. Thanks for marking it. Helps us find the unanswered.