topic Re: Calculating the average in QlikView

Calculating the average

Mon, 31 Oct 2011 13:26:21 GMT

Dears,

I need a help about the average calculating methodology.

I have a spreadsheet where on the

column D, named "percentage", there are some percentages values, like 80% or 30%, some blank cells, and some "---" text values.

So, I would like to create and expression to calculate the average of this column, and for this the expression should eliminate the blank cells and the "---" texts values.

How can I do it?
Can you give me an example?

Thanks

Re: Calculating the average

swuehl — Mon, 31 Oct 2011 13:39:34 GMT

Hi,

so you have something like this?

LOAD * INLINE [

ColA, ColD

A, 60%

B, 30%

C, sdfsdf

E, ----

F, 120%

];

Maybe a

=avg(ColD)

will just do.

Calculating the average

Mon, 31 Oct 2011 13:46:16 GMT

Nop,

Because with the avg(COLID) for example, the blank and --- values will be computated. I just want to calclulate the cells that have the % values.

Thanks for your attention!

Calculating the average

swuehl — Mon, 31 Oct 2011 13:51:39 GMT

Well in my attached example, those values will not be taken into account (I assume for the count of records).

I think the average is correct, 210 % / 3 is 70%.

So I assume your data is different from my sample.

Could you post a sample with your data?

Regards,

Stefan

Re: Calculating the average

Mon, 31 Oct 2011 13:58:09 GMT

Attached is my excample.
Thanks!

Re: Calculating the average

swuehl — Mon, 31 Oct 2011 14:08:00 GMT

Hm, would be easier if you could post the excel file itself.

I tried to do something similar, still a plain avg(Percentage) seems to do the job.

BTW, I am currently using QV10 SR2.

Regards,

Stefan

Re: Calculating the average

Mon, 31 Oct 2011 14:24:33 GMT

Maybe this is the problem, coz I'm using Wlik version 8.5

Calculating the average

swuehl — Mon, 31 Oct 2011 14:29:43 GMT

I don't have 8.5 available here for testing.

Maybe try

=avg(if(isnum(Percentage),Percentage))