11 Replies Latest reply: Mar 23, 2016 12:23 PM by Stefan Wühl

# What I am doing wrong do get a correct Average and Median in QV?

Hi,

I hope someone can help me out here. I do not get correct Average and Median numbers for a simple case of two prices in 8 rows.

For example:

Date          Price

04.01.2016 70.88

14.01.2016 70.88

20.01.2016 70.88

21.01.2016 70.88

08.02.2016 68.60

22.02.2016 68.60

07.03.2016 68.60

14.03.2016 68.60

and I get an Average of 69.67 and Median of 68.60 instead of 69.74 for both.

I use this expression to get the average: aggr(avg(Price), F1, F2)

Thanks

• ###### Re: What I am doing wrong do get a correct Average and Median in QV?

avg(Price) alone what does it give you?

• ###### Re: What I am doing wrong do get a correct Average and Median in QV?

The same. I use Aggr()  because I have multiple records in a pivot table.

• ###### Re: What I am doing wrong do get a correct Average and Median in QV?

I believe it's not useful to discuss a deviation between your expectation and the results if we don't know the setting you are using, e.g. how F1 and F2 relate to Price field.

• ###### Re: What I am doing wrong do get a correct Average and Median in QV?

Yes, I like this as well. It is not that easy as this is only one of tens of objects used in a big QVapp.

I'll see

• ###### Re: What I am doing wrong do get a correct Average and Median in QV?

Just a subset of much more dimensions and measures available. Where is the catch?

• ###### Re: What I am doing wrong do get a correct Average and Median in QV?

Hard to tell exactely, but I guess it's the differenc between your transactional average and the average grouped by your dimensions:

Average – Which average?

I believe what you really want is the average based on the Sum(Sales) / Sum(Qty).

• ###### Re: What I am doing wrong do get a correct Average and Median in QV?

Have you tried straight average?

Avg(Price)

• ###### Re: What I am doing wrong do get a correct Average and Median in QV?

Attached is one example with strange output, but in this case the Avg() returns correct number.

• ###### Re: What I am doing wrong do get a correct Average and Median in QV?

Could you detail what you consider a strange output?

Note that sum(Qty*Price) / Sum(Qty) is using a weight of Qty.

• ###### Re: What I am doing wrong do get a correct Average and Median in QV?

Sorry. You are correct about this one. I am just confused between results I get in QV and Excel for Average and Median.

Henric's post also brings in confusion that

If you want to display an average number of something in QlikView, you should use the Avg() function, right?

Wrong.

I just want to display to users Average and Median Price only for certain dimensions. In the end I decided to use just avg(distinct Price) and not the Median due to incorrect results.

Thanks

• ###### Re: What I am doing wrong do get a correct Average and Median in QV?

An average calculation using avg() is not wrong per se, Henric's point is that you need to be clear about what he calls

"implicit internal grouping entity".

What is this entity in your chart?