I have a set of data containing incoming calls for the past 3 months and I am attempting to create a line chart showing the incoming calls per hour for today as well as the average incoming calls per hour for the past 42 days.
Here is where I am at... I have a line chart with dimensions hour(inc_call_time) with a single expression of count(distinct(if(inc_call_time>=timestamp(today()),call_id)))
Now I am attempting to calculate the average for each hour of the day based on the past 42 days and I have had zero luck. Is it possible with an expression if the date is not a dimension?
I can't access the community from the network I have the file in so I can't provide sample data. I could probably fake something if someone really needs it.
Ok, first time doing an inline table. Let me know if this works for you.
I now want to add a line expression that shows a data point for average calls during that hour based on past days.
The below values aren't the same as in the data set but I tossed them together to help illustrate what I want to do. Today, 2/15/2013, shows 78 calls at hour 8 and I want an average data point to show 66 calls (calculated from the table below excluding today and rounded down)
