Script Error Null value

[#1]

Hi All,

In the below script

(avg(if([TEST VALUE]>90 and [TEST VALUE]<=100 , [TEST VALUE]))*Avg(Weight)/100) +
(avg(if([TEST VALUE]>80 and [TEST VALUE]<=90 , [TEST VALUE]))*Avg(Weight)/100) +
(avg(if([TEST VALUE]>70 and [TEST VALUE]<=80 , [TEST VALUE]))*Avg(Weight)/100) +
(avg(if([TEST VALUE]>65 and [TEST VALUE]<=70 , [TEST VALUE]))*Avg(Weight)/100) +
(avg(if([TEST VALUE]>30 and [TEST VALUE]<=65 , [TEST VALUE]))*Avg(Weight)/100) +
(avg(if([TEST VALUE]<30 , [TEST VALUE]))*Avg(Weight)/100)


The last two values which is on green color has no data so the above script returns empty on my chart, if I comment the last two lines out it is giving me proper Total of first four groups...

Please advice me on what i am missing

[marcus_sommer · Accepted solution]

You need for this rangesum() which returned 0 instead null if a calculation has no numeric return.

rangesum(Expression1, Expression2, ....)

- Marcus

[#3]

Thanks Marcus

Rangesum ((avg(if([TEST VALUE]>90 and [TEST VALUE]<=100 , [TEST VALUE]))*Avg(Weight)/100) +
(avg(if([TEST VALUE]>80 and [TEST VALUE]<=90 , [TEST VALUE]))*Avg(Weight)/100) +
(avg(if([TEST VALUE]>70 and [TEST VALUE]<=80 , [TEST VALUE]))*Avg(Weight)/100) +
(avg(if([TEST VALUE]>65 and [TEST VALUE]<=70 , [TEST VALUE]))*Avg(Weight)/100) +
(avg(if([TEST VALUE]>30 and [TEST VALUE]<=65 , [TEST VALUE]))*Avg(Weight)/100) +
(avg(if([TEST VALUE]<30 , [TEST VALUE]))*Avg(Weight)/100)) works like a charm for me .

Cheers...!