BusinessObjects Board

Webi Average Function

building reports
#1

Hi all,

Could anyone help with using an average function in webi report.

Essentially I am trying to create an average measure variable that will average the contents of 3 columns.

e.g.
Column1, Column2, Colum3, Average
5, 5, 5 ,5
5, 4, 5, 4.75
5, , , 5

In excel this would be =AVERAGE(A2:C2) or =AVERAGE(A2,B2,C2)

I have tried the following
=Average([Column1];[Column2];[Column3])
=Average([Column1],[Column2],[Column3])

Could anyone suggest the best syntax here?

Thanks very much!

#2

Hi @detod,

Welcome to B :mrgreen: B !

Average function doesn’t work for multiple columns. Here’s a simple workaround, create the below variables for your requirement.

v_Column 1 = If(Isnull([Column 1)) Then 0 Else [Column 1]
v_Column 2 = If(Isnull([Column 2)) Then 0 Else [Column 2]
v_Column 3 = If(Isnull([Column 3)) Then 0 Else [Column 3]
v_Average = ([v_Column 1]+[v_Column 2]+[v_Column 3])/3

Hope that helps!

Thanks,
Mohammed

#3

Hi Mohammed,

Thank you for the welcome and for your suggestions. This worked for me however I seem to be having a knock-on problem when I use the v_average variable. Not sure if you have any thoughts on this one.

I want to perform a count on rows from the v_average table where the average is >4.8

In the above example if I use

=Count(RowID]) Where ([v_Average] > 4.8)
I get a count of 3, whereas I expect 2

If I do a count on the other variable then that works
=Count(RowID]) Where ([v_Column 2] > 4.1)
I get 2 rows

Do you have any suggestions here?

Thanks a million!