I'm trying to create a table that shows the average number of distinct employees that visit the gym per hour each day. A day only consists of 3 hours. My data consists of records that are created each time an employee swipes his badge, which looks like...
A straight table of distinct counts by hour and day would look like...
Note that the above table includes hour 1, even though nobody visited the gym during hour 1 on either day 1 or day 2. Then taking the average of the distinct counts over the Day dimension...
|Hour||Avg Daily Distinct Count|
This is the table I'm looking for. What dimensions, expressions, and other properties do I need to make this table?
Solved! Go to Solution.
Thank you for your response, but your suggestion and your example do not achieve the desired result. In the example you posted (with different data), the averages are not correct. Given the data you loaded, the final table should read:
Take Hour 2, for instance. Your expression returns 1 as the average number of visits for Hour 2 when the actual average is 2/3. Think of it this way: On Day 1, there were 2 distinct visitors during Hour 2. On Day 2, there were 0 distinct visitors during Hour 2. On Day 3, there were 0 distinct visitors during Hour 2. The average number of visitors during Hour 2 over Days 1-3 is (2+0+0)/3 = 2/3.
That's very close. There's just one more detail to work out. I tried adding a duplicate line to your inline table and the table calculations were off. Here's what I mean...
The line I added was
Dana, 1, 1
which is exactly like another record in the inline table. Since I only want to count visits by distinct employees in a given hour on a given day, this should not have changed the final average calculation. But if you try it, you can see that the calculation for Hour 1 changes from 1/3 to 2/3 because it's counting both of Dana's visits in the same hour and day. Do you know how we can fix this?