Thanks Sunny for your quick response, however it is not working. Just to update on this I just checked that the data type of my field in SQL DB is nvarchar(max). Is it because of the defined data type that QS is not able to calculate the char average.
So, i just quickly check any field with 'int' data type average is calculate.
Thanks Anil , It worked when I included the above expression in script editor.
Before I was only using Set NullValue = ''; which was not working for me.
Again I want to update you it due to nvarchar data type in my DB table column Rating which QS was not able to calculate the char average. when i changed the data type of Rating column to Float then i easily applied Avg(Rating) expression and it worked.
I think it works in qlik even if it is a char/varchar.
I tested this with xls by formatting as a text field.
I need to get clarity on one point. When you are calculating Avg, do we need to consider Null values also.
for ex: In your example
If we do avg - it returns 1.17 (17.6/15)
but actually it should be 0.83 (17.6/22) as there are total 22 values and the sum is 17.6
Avg(if(Rating = 'NULL' or Rating = '' or len(Rating) = 0, 0, Rating))