topic Re: Trendline AVG Calculation in QlikView

Trendline AVG Calculation

Anonymous — Thu, 26 Oct 2017 15:06:08 GMT

Hello,

the attached example shows the worked hours made by some employees; in the chart is shown the calculated AVG (the green reference line) and the Average trend line (the red one).

The sum of the hours is 60 (according to the hours line of the graph), so the right result for the average is 15 (as shown by the AVG formula).

Could be someone so kind to explain me the average 20 result by the trendline calculation?

Thank you.

Re: Trendline AVG Calculation

isorinrusu — Thu, 26 Oct 2017 15:20:00 GMT

Hi Nicola,

The problem is that George has 2 imputations of 10 hours. So the avg is (10 + 10 + 20+ 20)/4.

What you need is this: AVG(aggr(sum(Hours),Employ)).

Regards,

Sorin.

Re: Trendline AVG Calculation

Anonymous — Thu, 26 Oct 2017 15:28:14 GMT

Hi Sorin,

thank you for the prompt replay; the average is (25+15+10+10)=60/4=15, in fact my question was about the wrong average trendline which shows 20, not about the AVG formula shown in the reference one (which shows 15, correctly).

Re: Trendline AVG Calculation

sunny_talwar — Thu, 26 Oct 2017 15:29:55 GMT

May be use an expression to create a line for average may be

=Sum(TOTAL Hours)/Count(TOTAL Employ)

Re: Trendline AVG Calculation

Anonymous — Thu, 26 Oct 2017 15:36:01 GMT

Thank you for the reply, Sunny,

your formula gives 15 as result (and It is the right result...I Know it).

It seems I'm not able to explain what is my question....I'd like to know if anybody can explai to me the formula used by QV to calculate the Average Trendline because it seems wrong to me.

Thank you.

Re: Trendline AVG Calculation

Anonymous — Thu, 26 Oct 2017 15:36:26 GMT

I think it's summing the hours which equals 60. And dividing by unique employs which is 3.

60/3 = 20.

I think using a trendline is erroneous when you have that kind of dimension. typically you would use a trendline over a chronological time period.

actually using a line chart is appropriate either. you should probably use a bar chart.

Re: Trendline AVG Calculation

sunny_talwar — Thu, 26 Oct 2017 15:38:00 GMT

I think QV is calculating it like this

Sum(Hours) = 60

Count(DISTINCT Employee) = 3

Sum(Hours)/Count(DISTINCT Employee) = 20

Re: Trendline AVG Calculation

Anonymous — Thu, 26 Oct 2017 15:38:24 GMT

I agree with you, Wallo....but what kind of average is this?

Re: Trendline AVG Calculation

Anonymous — Thu, 26 Oct 2017 15:39:03 GMT

See my edited reply

Re: Trendline AVG Calculation

Anonymous — Thu, 26 Oct 2017 15:40:05 GMT

If your axis was a date, I think that kind of average trendline would work out perfectly.

Re: Trendline AVG Calculation

Anonymous — Thu, 26 Oct 2017 15:42:49 GMT

In the attached example I used a bar chart, but the average results are the same. 😞

Re: Trendline AVG Calculation

Anonymous — Thu, 26 Oct 2017 15:45:38 GMT

Nope if you have two dates equal themselves with two different hour values

Re: Trendline AVG Calculation

sunny_talwar — Thu, 26 Oct 2017 15:51:38 GMT

I know you are not satisfied with the average which is calculated, but that is how it is... to read more on the topic of averages, you can read here:

Average – Which average?

Re: Trendline AVG Calculation

Anonymous — Thu, 26 Oct 2017 15:56:30 GMT

it's because you shouldn't really use a 'trendline'. for that kind of dimension, you should use a reference line.

it may not be what you want it to do, but it's summing your expression total (60) and dividing by your unique dimension values. Which if you look at the values in the chart display, that is what you're presenting.

Re: Trendline AVG Calculation

isorinrusu — Thu, 26 Oct 2017 16:09:20 GMT

Hi Nicola,

It does what the aggr(...) formula does. It calculates the mean for Employee after summing the hours (i.e. your expression) for each Employee.

So the trendline is calculated there as (15+20+25)/3.

If your expression was avg(Hours), the trending line would be calculated as (15+10+25)/3.

Regards,

Sorin.

Re: Trendline AVG Calculation

Anonymous — Fri, 27 Oct 2017 06:05:51 GMT

Thank you to everybody for your explanations!