I have two line graphs that show aggregate data over time windows, one that is intended to show aggregate total across the selected time window and another that needs to show the average over a time window.
For example:
Show the total record counts and total summed value for similar month across multiple years. (i.e. total of all jan, total of all feb, etc)
Show the average record counts and average values of all similar months across multiple years. (i.e. average of all jan, average of all feb, etc)
I need to do this across user selected time windows of Hour, Day, Week, Month, Quarter and Year
Time windows are defined as alternate dimensions for the chart using =Day(Timestamp), =Week(Timestamp), =Month(Timestamp), etc
For the sum aggregate Y axes are count(index) and sum(value) this seems to work for all of the time windows.
Conversely I cannot get the average to average across the selected time window. avg(aggr(count(index), timestamp))) results in empty values (technically '-', which I assume is NaN). I am able to get it to work for a single dimension as avg(aggr(count(index), Month(timestamp))), but I really need to average to respect the selected dimension.
Is there a way to get this to calculate aggregate averages over time series?
Attached is a sample qvf that expresses the issue.
