Average on chart calculating incorrectly

[#1]

I'm new to QlikView and came across a problem I don't know how to solve. I have a chart that is showing aggregate data by month - the data is displaying correctly. However, the average line as well as the displayed average amount are showing incorrectly - the average shown is 713, but the actual average for the time period is 1188.25.

When I manually select the date range included in the actual data associated with this chart, the average shows correctly.

Is there a way to set up the chart to only look at the correct date range even when all selections are cleared and the date dimension goes further back in time than I need in the chart?

Thanks!

[Gysbert_Wassenaar]

You probably want the average of the monthly values. I don't know the expression you use to calculate the monthly values. You need to embed that expression in the aggr function before you can calculate the average of it over the months: avg(aggr( ...your_expression_here... ), MonthYear))

And then you're using a cycle group, so the chart dimension will change. So you need something like avg(aggr( ...your_expression_here... ), $(='[' & GetCurrentField('CycleGroupNameHere') &']')))

[#3]

Thanks for the feedback. Here is the average expression I am using currently:

Avg(AGGR(Count(DISTINCT Fix.Key),Cal.MonthYear)).

Cal is the date dimension and Fix is the dimension I'm measuring in the graph.

[mikecrengland]

Hi Gysbert -

(I work with Brad) I don't think he emphasized enough the date problem. We have some data that starts in June, but the data he's trying to get an average on starts in September. What I think is happening is that the aggregation function is counting June, July and August in the average, bringing it down. We can't figure out how to eliminate those 'empty' months when selections are cleared.

Thanks!

mike

[Gysbert_Wassenaar]

If you want to limit the months that the average is calculated over you can use a set analysis expression. Perhaps this one: Count({<Cal.MonthYear={'Sep-2014'}>}DISTINCT Fix.Key)

[mikecrengland]

Thanks, Gysbert!

Just in case anyone else might need the syntax:

=IF(GetCurrentField(MonthWeek) = 'Cal.MonthYear',

  Avg({<FullDate = {">2014-08-01"}>} AGGR(Count(DISTINCT Fix.Key),Cal.MonthYear)),

  Avg({<FullDate = {">2014-08-01"}>} AGGR(Count(DISTINCT Fix.Key),Cal.WeekStartMon))

  )

mike