Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
johnmorgan
Partner - Contributor III
Partner - Contributor III

Aggregate over user selected time series

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.

 

2 Replies
cengizeralp
Contributor III
Contributor III

Hi Johnmorgan,

Could you change your expression

from "avg(aggr(count(index), timestamp)))"

to       "avg(aggr(count(Index), timestamp)))" 

please.

"index" is a function but "Index" is a field of your table. I think there is a wrong choice.

johnmorgan
Partner - Contributor III
Partner - Contributor III
Author

That made no difference.  I also tried adding square brackets around both Index and Timestamp (since I believe they are key words.  Sorry it was a quick sample since I cannot upload the real application due to IP and data constraints.

 

I can completely change the names and it makes no difference on the calculation of the average.  Essentially what I really am looking to do is change the second parameter of the aggr function based on the selected alternate state.  However GetObjectValue, GetObjectDimension[0] do not seem to work.