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?

[qliksus]

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

[#3]

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

[#4]

Hi,

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

--

Vinoth

[#5]

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)

[Anonymous]

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

[#7]

I comment string

//WHERE NOT IsNull(NUMERICALRESULT)

and nothing has changed.

I tried

WHERE NOT len(trim(NUMERICALRESULT))>0

there is nothink in Everage.

[Anonymous]

Sorry, my mistyping , there is no NOT:

WHERE len(trim(NUMERICALRESULT))>0

[#9]

Don't worry 😃

I remove NOT but calculations were wrong

[Anonymous]

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