Average of numeric field

[Ipsita]

Hello,

Can anyone please help me in calculating the average of below shown column. It is strange why QS is not able to calculate the average of a number field. I am using SQL server for data connection and uploading to QS Desktop via Data Manager. All the Null values are from Database table.

I can only able to calculate the count of the 'Rating' by giving the expression Count(Rating) as measures in KPI visualization.

If i will use excel data sheet for the same column then it is able to calculate average but not for my Data connection.

Any help/suggestion is highly appreciated.

Regards,

Ipsita

[sunny_talwar]

May be this

Avg({<Rating -= {'NULL'}>} Rating)

[Ipsita]

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.

Please advice.

Regards,

Ipsita

[sunny_talwar]

Not sure I completely follow, would you be able to share a sample?

[Anil_Babu_Samineni]

Or May be this for understand

Set Nullinterpret = 'NULL';

NullasValue Rating;

Set NullValue = '';

Load * From <Data Source Table>;

And Expression in text object like Avg(Rating)

POS - Even, If you have NULL directly then i think Avg() function deserve, Aren't you?

[d_prashanthredd]

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(Rating)

Avg(if(Rating = 'NULL' or Rating = '' or len(Rating) = 0, 0, Rating))

[Ipsita]

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.

[Anil_Babu_Samineni]

Even, DB what ever Data type we are using that's not a matter for Qlik. That Qlik can handle those at a moment because Qlik is good SQL engine and it can measure and initiate to single.

[Ipsita]

Agreed, but then why it could not able to calculate when my data type was nvarchar. when i changed the data type to float it worked without any other change.

I used you code in another app which i copied.

[Anil_Babu_Samineni]

Because, VarChar() consider both (String + Digit) and Float (Only Digits). If, you are entering as NULL for missed numbers then varchar needed from DB side. If you are skipping that and using simple Float then it works.

Does it make sense?