help with avg()

[#1]

Hi.

The result of the function avg() deviates by a small value. The result of the func sum() and count() are correct in the same table and sum()/count() are deviant either. For example:

field	sum(field)	count(field)	avg(field)	sum/count
1.00	5	4	1.34	1.34
1.00
2.00
1.00

request:

LOAD

  TESTGUID,

  AVG(NUMERICALRESULT) AS 'NUMRESAVG',

  SUM(NUMERICALRESULT) AS 'NUMRESSUM',

  COUNT(NUMERICALRESULT) AS 'NUMRESCOUNT'

Resident TRES

WHERE NOT IsNull(NUMERICALRESULT)

Group BY TESTGUID;

What's wrong?

[#2]

There is a view QlikView (.qvw) file?

[Anonymous]

Yes, can you upload the qvw file?  Not necessarily your actual file, maybe a simplified sample with some data that allows to reproduce the problem.

[#4]

http://www.fayloobmennik.net/3370460

I'm sorry for design 😃

[#5]

No suggestions?

[Anonymous]

Yes, you got it right - the design is the problem.  It is pretty much messed up, and the table TRES is a loosely coupled table in your data model.  I don't even attempt to predict how it may work.  Apparently it was not intentional - QV loosened it to break the logical loops.  Besides, you have synthetic keys, which is usually a symptom of a poor deign.

So, the step one for you is to re-design the whole thing, create a more or less clean data model.  I guess you need help from a local QV consultant who can spend enough time to understand the purpose and the data.  Or, you need to spend time getting QV training.  Maybe it is the best way because you know the data and the purpose already.

Regards,

Michael

[Clever_Anjos]

I was checking your application.

Did you noticed that your NUMERICALRESULT is a decimal not an integer?

At your first example, maybe your sum(Field) is not exactly 5, but another value like 5,36?

[#8]

Thanks for help. I fix my script. Problem persists. I found what was wrong. If to use that:

= if(field > 0, avg_field, ' ')

the result was wrong as I wrote above. Correctly:

= if(field > 0, avg_field)

(without "else" state)

May be for someone else's obvious, but I did not realize.

Thank you for your attention.