topic Re: help with avg() in QlikView

help with avg()

Thu, 21 Nov 2013 12:53:42 GMT

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?

Re: help with avg()

qliksus — Thu, 21 Nov 2013 13:41:47 GMT

can you little elaborate ur req? What is ur expected output for the above data.

Re: help with avg()

Thu, 21 Nov 2013 13:46:02 GMT

Hi,

Can you try to create a Statistics Box (New Sheet Object -> Statistics Box) and apply all the required aggregations(Sum,Avg,Count...).

This will give you the exact data till the required decimals.

Thanks,

Sai

Re: help with avg()

Thu, 21 Nov 2013 13:46:40 GMT

Hi,

Could you please share the source file to have a look for better understanding?

Vinoth

Re: help with avg()

Thu, 21 Nov 2013 16:25:09 GMT

important part:

//....

TRES:

SQL SELECT RESULTGUID, TESTGUID, SAMPLEGUID, RESULTID, NUMERICALRESULT, TEXTRESULT,

UNIT, RESULTTYPE, SEQUENCENUMBER

FROM ELNPROD.TESTRESULT;

//...

TRES2:

LOAD

TESTGUID,

AVG(NUMERICALRESULT) AS 'NUMRESAVG'

SUM(NUMERICALRESULT) AS 'NUMRESSUM',

COUNT(NUMERICALRESULT) AS 'NUMRESCOUNT'

Resident TRES

WHERE NOT IsNull(NUMERICALRESULT)

Group BY TESTGUID;

//...

Average(=NUMRESAVG)

Results(=if(isNull(NUMERICALRESULT), TEXTRESULT, NUMERICALRESULT & ' ' & UNIT)

Re: help with avg()

Anonymous — Thu, 21 Nov 2013 16:32:10 GMT

There could be an issue with isnull(). Can you try this instead:

WHERE NOT len(trim(NUMERICALRESULT))>0

Edit: I'd rather don't create this TRES2 at all, and use front-end calculations directly in the table. But maybe you have reasons which I don't know...

Re: help with avg()

Thu, 21 Nov 2013 16:40:01 GMT

I comment string

//WHERE NOT IsNull(NUMERICALRESULT)

and nothing has changed.

I tried

WHERE NOT len(trim(NUMERICALRESULT))>0

there is nothink in Everage.

Re: help with avg()

Anonymous — Thu, 21 Nov 2013 16:53:07 GMT

Sorry, my mistyping , there is no NOT:

WHERE len(trim(NUMERICALRESULT))>0

Re: help with avg()

Thu, 21 Nov 2013 17:14:40 GMT

Don't worry 😃

I remove NOT but calculations were wrong

Re: help with avg()

Anonymous — Thu, 21 Nov 2013 18:36:59 GMT

It is not clear without an example... Can you upload a sample application?

Re: help with avg()

Thu, 21 Nov 2013 18:45:00 GMT

There is a view QlikView (.qvw) file?

Re: help with avg()

Anonymous — Thu, 21 Nov 2013 20:00:15 GMT

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.

Re: help with avg()

Fri, 22 Nov 2013 11:28:36 GMT

http://www.fayloobmennik.net/3370460

I'm sorry for design 😃

Re: help with avg()

Sun, 24 Nov 2013 11:26:56 GMT

No suggestions?

Re: help with avg()

Anonymous — Sun, 24 Nov 2013 21:22:27 GMT

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

Re: help with avg()

Clever_Anjos — Sun, 24 Nov 2013 21:45:43 GMT

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?

Re: help with avg()

Tue, 26 Nov 2013 06:15:47 GMT

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.