Set analysis to exclude zero in average value

[#1]

Hello,

I have two set analysis as follows and would like to know how I could combine them to show the average value but also remove zero (0) values as this affects the total average:

Expression 1: avg({$< [S1]={'A'}, [S2]={'B'}, [S3]={'C'}, S4={'D'}>}ResultsNum)

Expression 2: avg({$< [X1]={'M'}, [X2]={'N'}, [X3]={'O'}, X4={'P'}>}ResultsNum)

Thank you.

[#2]

Thanks Ruben,

There were no errors in the expression so that's good start. Having used your suggestion, all the results are returning as 0.

Below is a sample of the table from which I'm trying to find the average. In the original spreadsheet, there are no zeros (0) but just blank spaces. S1, S2, S3, S4 and X1, X2, X3, X4 are the set of conditions for the set analysis.

You would notice that for the 04/07/2015 there is blank space as there were not results entered that day. I am sure you would agree that if a zero was entered for that day the average result would not be 6096. The source data has blanks in the cells which have no results (maybe this is what I should have mentioned earlier). What I'm trying to achieve therefore is to have an expression which ignores blank results when calculating the average values.

DATE	Location1	Location2	AVG
	S1	X1
	S2	X2
	S3	X3
	S4	X4
01/07/2015	4478	5546	5012
02/07/2015	4685	3760	4223
03/07/2015	4485	4037	4261
04/07/2015		6096	6096

Thanks in advance,

[rubenmarin · Accepted solution]

Hi Mike, so you want to include or exclude blank values?, I think avg by default ignores not-number values

In that table Avg can be calculated as =rangeavg(Location1Expression, Location2Expression), the last row will return 6096.

If you looking for 6096 in the last row maybe your expression should be this:

RangeAvg(avg({$< [S1]={'A'}, [S2]={'B'}, [S3]={'C'}, S4={'D'}>}ResultsNum),

         avg({$< [X1]={'M'}, [X2]={'N'}, [X3]={'O'}, X4={'P'}>}ResultsNum)

)

This will work using date as dimension, if you want an Avg() of the AVG column outside that table (textbox, totals row,...) it can be:

Avg(Aggr(RangeAvg(avg({$< [S1]={'A'}, [S2]={'B'}, [S3]={'C'}, S4={'D'}>}ResultsNum),

         avg({$< [X1]={'M'}, [X2]={'N'}, [X3]={'O'}, X4={'P'}>}ResultsNum)

), DATE))

If you looking for 3048 in the last row maybe your expression should be this:

(avg({$< [S1]={'A'}, [S2]={'B'}, [S3]={'C'}, S4={'D'}>}ResultsNum)

+ avg({$< [X1]={'M'}, [X2]={'N'}, [X3]={'O'}, X4={'P'}>}ResultsNum))/2

[#4]

Ruben,

The first option worked perfectly well (arriving at 6096) and that was what I was looking for. Thanks very much!