
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
calculate average distinct days
Hi
I'm working on an app and I'm stuck.
the app needs to measure traffic on phone lines
So I've got an excel sheet with a date column, a time column (I've created a script to round this to hour). Trunk is a column with a unique number for each call and the day[date] column is also created in the script.
I'm trying to create a chart where you would be able to see how many calls were received on average by the hour.
You can get into more detail by using filters such as year, quarter, month etc.
So i tried this
Dimension hour[time]
measurement count(Trunk)/count(distinct([Day([Date]])]))
This gives a different result depending on the selection of years, months etc
a small difference would be normal, but I'm getting results like 20 per hour if on a monthly basis and over 200 an hour if I look at a yearly basis.
So it's fair to say that I'm stuck.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May be this
Sum(Aggr(Count(Trunk)/Count(DISTINCT Day(Date)), Month, Year))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May be this
Sum(Aggr(Count(Trunk)/Count(DISTINCT Day(Date)), Month, Year))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Did you try use change the "COUNT" for "SUM" at this place "COUNT(Trunk)"??


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Wannes,
Maybe:
Avg(Aggr(Count(Trunk),Date,Hour))
Where Hour is created in your script by Hour(Time) and Hour used as your chart dimension.
Good luck
Andrew
