I have a dataset looks like this:
I want to count the average number of distinct users by hour of the day.
{hour_of_day} is calculated as
{avg_distinct_viewer_by_hour_of_day} is calculated below. I add up the distinct number of ID by day and hour, and then divided by the number of distinct days
The result works in a table format
But I can’t change it into a line graph and it has the error below:
Looking for some help in the calculation. Thanks!
Thank you for providing the detailed information and screenshots. I can see that you have a dataset with timestamp data and you want to calculate the average number of distinct users by hour of the day.
The issue you’re facing with converting the result to a line graph is likely due to the way the calculation is being done.
The key steps to calculate the average number of distinct users by hour of the day would be:
Calculate the hour of the day: You’ve already done this, and it looks correct.
Count the distinct users per hour: Instead of aggregating the distinct users by day and hour, and then dividing by the number of distinct days, you should directly count the distinct users per hour.
Calculate the average: Once you have the distinct user count per hour, you can simply take the average of those values.
Here’s the step-by-step approach:
Create a calculated field to get the distinct user count per hour:
distinct_users_per_hour = COUNTDISTINCT([ID], [hour_of_day])
Create another calculated field to get the average distinct users per hour:
avg_distinct_users_per_hour = AVG([distinct_users_per_hour])
Use the avg_distinct_users_per_hour field in your visualization, and it should now work as a line graph.
The key difference here is that you’re directly calculating the distinct user count per hour, rather than aggregating by day and then dividing. This should give you the desired result and allow you to visualize it as a line graph.
Please let me know if you have any further questions or if you need any clarification on the steps.
Hi @murili Thank you for your reply! I tried the calculations but the result doesn’t seem to be correct. This counts the distinct IDs by hour but it didn’t consider how many days are there so the averaged results is much higher than the actual average users per hour.
Still looking for some help on this question. Thanks in advance!
@ kiko, maybe there is null values vs blank in the dataset.
Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the Quick Sight Community!
In case you need further assistance with your problem, please create a sample dashboard with sample dataset showing your problem state using Arena and please create a new post, so we can look at it from a fresh perspective. (Details on using Arena can be found here - Quick Sight Arena)