Average calculation not correct

[#1]

Hi Qlikview developers,

I need some help to see what is going one here...

I have a pretty simple datamodel (to isolate the problem) and a .qvw in which I try to explain what I think is very strange... I hope someone of you can explain me what is going on...

This is my case

- I have Bookings and articles (1 - N)

- I want to show in a gauge (or textbox for this purpose) the average total price per booking

- when I calculate this with avg(aggr(sum(Prijs),Reserveringnummer)) this gives another outcome than Sum(Prijs) / count(distinct Reserveringnummer)

I can't explain that, since in this case it should produce the same outcome in my qvw (all articles belong to 1 reservering)

It becomes even stranger: when I do not load my master-calendar... the avg(aggr(sum(Prijs),Reserveringnummer))  expression _suddenly_ does give the right result...

I hope someone can show me what I'm overlooking (or can confirm that this seems to be a bug?)

I hope my attached QVW is clear enough. Let me know if there are any further questions.

Thanks in advance!

[Gysbert_Wassenaar]

There's a null value somewhere because of the join. If you change the expression to =avg(aggr(sum({<Prijs={'*'}>} Prijs),Reserveringnummer)) the average is what you expect.

[#3]

Hi Gysbert,

Thanks for your reply... I agree that your expression does produce the correct result... But my first answer was like yours (somewhere there is a nullvalue). I however cannot find where that nullvalue should be... (after looking for it for quite some time).

I checked this in my datamodel

- Prijs is never "null" or 0

- there are no article (/reserveringartikel) records that do not belong to a reservering record...  (there are exactly 54 article lines... and 20 reservering lines and they are referential integer... (no orphans)

So

Question 1: do you have any idea where this null-value comes from in this example?

Question 2: what has my master calendar to do with that expression? In my opinion it is totaly unrelated...

[Gysbert_Wassenaar]

Your Calendar contains far more dates than there are dates in Reserveringen. That means if the two tables are joined you'll have a lot of nulls for the fields from Reserveringen. Those nulls are aggregated too. So there are 21 aggregations for Reserveringnummer while count(distinct Reserveringnummer) has 20 as result since count doesn't count nulls. Tricky, isn't it

[#5]

Ok, Thanks for your explanation.

To be honest, I find this quite silly behavior.

- I'm aggregating over the field "reserveringnummer" and then expect only a value (sum(Prijs) )for each reserveringnummer that exists in the datamodel. For this calculation (IMO) the calendar object does not have to be joined at all. It should only work as a "filter" of reservering-records (...)

I learned this about the aggr that it works like this:

- the first argument is like the expression in your straight table

- all following arguments is like the dimensions in your straight table

So in my case I could create a straight table with dimension Reserveringnummer and sum(Prijs) as expression.

When I make this, I only see 20 lines (and not 21). And therefore I'm expecting the avg( ) function to produce the result for 20 records...

In other words: I can now understand (technically) a bit better what is happening, but stil find this strange behavior.

Thanks for the help anyway 🙂

[whiteline]

Hi

It's normal behaviour.

The matter is that "reserveringnummer" is a key field.

Since QV stores the data in a columns the relational structure that you see is virtual.

If you want a solution, just add

"reserveringnummer" as "reserveringnummerausreserveringartikel"

field when you load reserveringartikel table.

So that you could easily use it in aggr() expressions.

[#7]

Hi Whiteline,

Thanks for your response as wel...

I did what you suggested:

- I added Reserveringnummer as reserveringnummerausreserveringartikel  in table reserveringartikel

- I reloaded and tested this expression:

=avg(aggr(sum(Prijs),reserveringnummerausreserveringartikel))

It still returns the (incorrect) value (1.111,381). See also attached (I added it in red (upper right corner)).

Btw: I did try a similar thing myself, but then I added that field in the Reservering-table... No luck either.

It may be "normal behavior", but in the solution you suggest, Qlikview can get its info from the "reserveringartikel table" only.

For me it doesn't really make sense, but I get the idea...

[whiteline]

I didn't noticed that you have duplicates in reserveringartikel table.

See the screen below.

I guess it's obvious that Sum/Count <> Avg in this case.

[#9]

Hmz,my statement was that Avg() should be the same as count(distinct )

I still find the avg(aggr()) construction far from intuitive, since from your screenshot I would argue that the 23.339 should be divided by 20 (20 distinct "Reservingnummer") and not by 21 (due to some strange nullvalue).

I now know that I have to deal with it, but I hoped there was a good explanation for it.

[whiteline]

I see...

It's even more strange.

In a chart the total is calculated correctly while in a text box its wrong.