Calculate average but excluding zero entries

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Calculate average but excluding zero entries

Author

Topic

cidr
Posting Yak Master

207 Posts

Posted - 2010-02-03 : 06:38:38

Hi there,

Really hope someone can help me.

I've been asked to develop a report that is based on a survey. the survey has 4 questions (Q.1, Q.2 , Q.3 ,Q.4) each question gets marked between 1 and 4.

Lets say a employee gets marked for each question, I'd want to find the average for this which is easy:
=sum(fields!Q_1+ Q_2 +Q_3 +Q_4)/4)

This will go into the Average column

Each office gets marked and this is done by using the average for each employee for each question. So lets say office1 has three employees who all scored 3 for each question, for the Office row I could use:
Avg(Fields!Q_1.value) for each question column and use:
=avg((fields!Q_1.value + fields!Q_2.value + fields!Q_3.value + fields!Q_4.value )/4) on the Average column

However, the problem I have is that sometimes the data we have wont be filled in completlely and some questions will be blank. Because of this I need to find a way to ignore the entries that are null and only calculate the average for entries over zero

If two out of three employees had marks but one employee had no marks I'd only want to calculate the two employees. is this possible to do? and how would I go about doing it?

Many thanks for all help

Paul

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 06:52:24

avg(), sum() etc avoids null values. so if you want to ignore blank values also make sure you write a conditional expression inside avg() function like

Avg(iif(field!fieldname.value = "", Nothing,Field!Fieldname.value))

cidr
Posting Yak Master

207 Posts

Posted - 2010-02-03 : 09:48:50

Thanks for your help again Visakh

I tried the sum but I'm getting error:
=Avg(iif(fields!Q1_1.Value = "", Nothing,Fields!Q1_1.Value))

I'd still have to find the Average for all questions for the Office grouping which I think would be something like

=Avg(iif(fields!Q1_1.Value = "", Nothing,Fields!Q1_1.Value)
+
iif(fields!Q1_2.Value = "", Nothing,Fields!Q1_2.Value)
+
iif(fields!Q1_3.Value = "", Nothing,Fields!Q1_3.Value)
+
iif(fields!Q1_4.Value = "", Nothing,Fields!Q1_4.Value))/4

I heard to do this I'd need to use Sum and Count but I can't get the syntax correctly for this.

Do you know if there's still a work around?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 11:30:25

do you mean this?

=(Sum(iif(fields!Q1_1.Value = "", 0,Fields!Q1_1.Value)) + Sum(iif(fields!Q1_2.Value = "", 0,Fields!Q1_2.Value)) + Sum(iif(fields!Q1_3.Value = "", 0,Fields!Q1_3.Value)) + Sum(iif(fields!Q1_4.Value = "", 0,Fields!Q1_4.Value)))/(Count(iif(fields!Q1_1.Value = "", Nothing,Fields!Q1_1.Value))+ Count(iif(fields!Q1_2.Value = "", Nothing,Fields!Q1_2.Value)) + Count(iif(fields!Q1_3.Value = "", Nothing,Fields!Q1_3.Value))+ Count(iif(fields!Q1_4.Value = "", Nothing,Fields!Q1_4.Value)))

cidr
Posting Yak Master

207 Posts

Posted - 2010-02-10 : 07:30:30

Hey visakh

For the moment I've used ReportItems method to add the textboxes together, the text boxes it's adding are using avg as they are only averaging rows from that scope. it seems to work, however, it's still to be tested. I'll let you know how I get on and if there are any problems I'll move further with the code listed in this thread.

Thanks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 08:45:10

no problem
please let us know how u got on

------------------------------------------------------------------------------------------------------
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources