Solved: Re: Set analysis to exclude zero in average value - Qlik Community

Report Inappropriate Content · ‎2015-08-07

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.

rubenmarin · ‎2015-08-07

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

View solution in original post

jonathandienst · ‎2015-08-07

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

sunny_talwar · ‎2015-08-07

I think the issue has been resolved since SR11 or SR12. I know SR12 doesn't show -= as a syntax error anymore

sunny_talwar · ‎2015-08-07

Alternatively you can also try this:

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

sasiparupudi1 · ‎2015-08-07

This?

avg({$< S1={'A'},S2={'B'},S3={'C'},S4={'D'},X1={'M'},X2={'N'},X3={'O'},X4={'P'},ResultsNum={"<>0"}>}ResultsNum)

Anonymous · ‎2015-08-07

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

Report Inappropriate Content · ‎2015-08-07

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.

sasiparupudi1 · ‎2015-08-07

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)

Report Inappropriate Content · ‎2015-08-07

There seems to be an error with that expression.

rubenmarin · ‎2015-08-07

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)

)