Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
For expression 1
=avg({$< [S1]={'A'}, [S2]={'B'}, [S3]={'C'}, S4={'D'}, ResultsNum-={0} >} ResultsNum)
The -= will show up as a syntax error, but this is failure in the syntax checker and the syntax is correct.
I think the issue has been resolved since SR11 or SR12. I know SR12 doesn't show -= as a syntax error anymore
Alternatively you can also try this:
=Avg({$< [S1]={'A'}, [S2]={'B'}, [S3]={'C'}, S4={'D'}, ResultsNum = e({<ResultsNum = {0}>})>}ResultsNum)
This?
avg({$< S1={'A'},S2={'B'},S3={'C'},S4={'D'},X1={'M'},X2={'N'},X3={'O'},X4={'P'},ResultsNum={"<>0"}>}ResultsNum)
Hi Mike,
Dont forget that you are able to combine Set analysis and If statements. it is better to use Set analysis to perform "selections"and if statements to restrict the field you are aggregating on. so:
=avg({$< [S1]={'A'}, [S2]={'B'}, [S3]={'C'}, S4={'D'}>} if(ResultsNum<>0,ResultsNum))
Gareth
All of the suggestions made here have partly worked. Thank You!
The issue is that, for dates where there are zero values for expression 1 but not for expression 2 (and vice versa), these dates are completely removed from the resulting table. It appears that for the expressions that have been suggested here, when the result for expression 1 is zero and that for expression 2 is a valid number, no results are returned in the table.
What I would like to know is:
1. how do I combine both expressions to reflect the removal of zeros and
2. how do I avoid the removal of the end result for dates where expression 1 is zero while expression 2 is not (and vice versa)?
Thank You.
This ?
if(ResultsNum<>0, avg(avg({$<S1={'A'},S2={'B'},S3={'C'},S4={'D'}>}ResultsNum),avg({$<X1={'M'},X2={'N'},X3={'O'},X4={'P'}>}ResultsNum),0)
There seems to be an error with that expression.
Hi, maybe this?:
RangeAvg(avg({$< [S1]={'A'}, [S2]={'B'}, [S3]={'C'}, S4={'D'}, ResultsNum-={0} >} ResultsNum)
* count({$< [S1]={'A'}, [S2]={'B'}, [S3]={'C'}, S4={'D'}, ResultsNum-={0} >} ResultsNum),
avg({$<[X1]={'M'}, [X2]={'N'}, [X3]={'O'}, X4={'P'}, ResultsNum-={0} >} ResultsNum)
* count({$<[X1]={'M'}, [X2]={'N'}, [X3]={'O'}, X4={'P'}, ResultsNum-={0} >} ResultsNum)
)