Calculate average distinct number of ID by hour of day

[kiko]

I have a dataset that looks like this:

I want to calculate the average distinct number of ID by hour of day.

I calculated the {c_hour_of_day}

{c_avg_id_by_hour_of_day} is calculated to be sum of distinct number of ID by day and hour divided by the number of distinct hours of the day as:

The result works in a table format (top left table - Avg Distinct Number of ID by Hour of day), but it doesn’t work as a line graph. Error is shown below:

sample_avg_id_by_hour_of_day

Looking for some help in the calculations. Thanks!

[David_Wong]

Hi @kiko,

You don’t need to calculate the sum and then divide - you can calculate the average directly. Please take a look at my solution. I also used the truncDate function instead of formatDate.
Average of distinct number of IDs by hour of day.

[kiko]

Hi @David_Wong It worked. Thanks!