Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
ipsitafujitsu
New Contributor

Average of numeric field

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.

Capture.PNG

Any help/suggestion is highly appreciated.

Regards,

Ipsita

11 Replies

Re: Average of numeric field

May be this

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

ipsitafujitsu
New Contributor

Re: Average of numeric field

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

Re: Average of numeric field

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

Re: Average of numeric field

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?

Life is so rich, and we need to respect to the life !!!
d_prashanthredd
Contributor III

Re: Average of numeric field

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

ipsitafujitsu
New Contributor

Re: Average of numeric field

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.

Re: Average of numeric field

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.

Life is so rich, and we need to respect to the life !!!
ipsitafujitsu
New Contributor

Re: Average of numeric field

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.

Re: Average of numeric field

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?

Life is so rich, and we need to respect to the life !!!
Community Browser