topic Re: Average on chart calculating incorrectly in QlikView

Average on chart calculating incorrectly

Fri, 07 Aug 2015 14:45:55 GMT

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!

Re: Average on chart calculating incorrectly

Gysbert_Wassenaar — Fri, 07 Aug 2015 15:14:52 GMT

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') &']')))

Re: Average on chart calculating incorrectly

Fri, 07 Aug 2015 17:02:28 GMT

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.

Re: Average on chart calculating incorrectly

mikecrengland — Sat, 08 Aug 2015 16:37:25 GMT

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

Re: Average on chart calculating incorrectly

Gysbert_Wassenaar — Sat, 08 Aug 2015 16:44:24 GMT

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)

Re: Average on chart calculating incorrectly

mikecrengland — Mon, 10 Aug 2015 14:25:45 GMT

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