topic Re: Average calculation not correct in QlikView

Average calculation not correct

Tue, 27 Nov 2012 12:04:28 GMT

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!

Re: Average calculation not correct

Gysbert_Wassenaar — Tue, 27 Nov 2012 12:16:42 GMT

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.

Re: Average calculation not correct

Tue, 27 Nov 2012 12:22:25 GMT

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)

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...

Re: Average calculation not correct

Gysbert_Wassenaar — Tue, 27 Nov 2012 12:34:34 GMT

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

Re: Average calculation not correct

Tue, 27 Nov 2012 12:45:37 GMT

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 🙂

Re: Average calculation not correct

whiteline — Tue, 27 Nov 2012 13:50:08 GMT

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.

Re: Average calculation not correct

Tue, 27 Nov 2012 15:49:16 GMT

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...

Re: Average calculation not correct

whiteline — Tue, 27 Nov 2012 16:22:54 GMT

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.

Re: Average calculation not correct

Tue, 27 Nov 2012 16:30:31 GMT

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.

Re: Average calculation not correct

whiteline — Tue, 27 Nov 2012 16:58:49 GMT

I see...

It's even more strange.

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

Re: Average calculation not correct

Tue, 27 Nov 2012 17:56:03 GMT

Hehe thanks for sharing this finding.

Strange indeed (...).

Re: Average calculation not correct

whiteline — Tue, 27 Nov 2012 19:16:27 GMT

If you create the chart mentioned above and uncheck 'Supress Zero-values' and 'Suppress missing' you'll see that 'strange null' row.

upd:

Also it's something strange with %Datum key field. The screen is attached.

Re: Average calculation not correct

Tue, 27 Nov 2012 20:09:26 GMT

Indeed seems strange (the %Date field in your screenshot), although I don't seem to do very special things...?

In the attached QVW I made the table you suggested. The strange thing that I now discovered is that

- the expression total is "correct"

- the caption of this chart is incorrect

>>The caption and the total are both derived from one and the same expression...

See the .qvw and the screenshot attached to this post.

whiteline wrote:
If you create the chart mentioned above and uncheck 'Supress Zero-values' and 'Suppress missing' you'll see that 'strange null' row.

Sorry, I tried to make this "strange null row" visible, but I couldn't. As visible attached, those 2 checkboxes are unchecked (dimensions tab). What else should I do to make that one visible?

Re: Average calculation not correct

whiteline — Wed, 28 Nov 2012 06:36:31 GMT

The checkboxes on presentation tab are checked.

Uncheck them (See the screenshot).

The totals is correct, because these checkboxes filter the data set.

The expression for caption is calculated without that filter, so it includes that null values.

Re: Average calculation not correct

Wed, 28 Nov 2012 09:09:43 GMT

Thanks, now i found them (the right ones). I only knew from suppress null-values from the dimensions tab. Good to know.