Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
can you little elaborate ur req? What is ur expected output for the above data.
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
Hi,
Could you please share the source file to have a look for better understanding?
--
Vinoth
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)
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...
I comment string
//WHERE NOT IsNull(NUMERICALRESULT)
and nothing has changed.
I tried
WHERE NOT len(trim(NUMERICALRESULT))>0
there is nothink in Everage.
Sorry, my mistyping , there is no NOT:
WHERE len(trim(NUMERICALRESULT))>0
Don't worry 😃
I remove NOT but calculations were wrong
It is not clear without an example... Can you upload a sample application?