topic Re: How does Avg() handle strings in script? in QlikView

How does Avg() handle strings in script?

stefan87 — Wed, 25 Apr 2018 09:07:02 GMT

Hello Qlik Community,

I am using an Avg() function in my script, trying to calculate the average value of some numeric values (e.g. sales amounts).

However, some of my data contain text like 'N/A' which should not be taken into account when calculating the average value.

So my question is: Does Avg() ignore these values or does it sum them up as zero which will have an impact on the average?

Thank you very much in advance!

Stefan

sunny_talwar — Wed, 25 Apr 2018 12:45:15 GMT

AFAIK and based on testing =RangeAvg(1, 'NA', 2) = 1.5... Avg will ignore NA and find the average based on the numbers only.

keerthika — Wed, 25 Apr 2018 12:47:03 GMT

Hi,

There is no impact, If the field (sales amounts) has 'N/A' value.It doesn't affect the sales amount avg

stefan87 — Wed, 25 Apr 2018 13:50:31 GMT

Thank you both for answering. This was what I expected, just wanted to check if I was right!

Stefan

naveenkumarchin — Fri, 27 Apr 2018 18:24:44 GMT

Hi Stefan,

unfortunately, it impacts avg but not sum.

When you have strings or nulls, and using aggregations, qlik will consider them as "Zero" while performing aggregation.

So for Sum, you may not see any effect. but for Avg() I think it will effect.

Thank you,

Naveen