Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set analysis to exclude zero in average value

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.

1 Solution

Accepted Solutions
rubenmarin

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

12 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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

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

sunny_talwar

Alternatively you can also try this:

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

sasiparupudi1
Master III
Master III

This?

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



Anonymous
Not applicable
Author

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

Not applicable
Author

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
Master III
Master III

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)

Not applicable
Author

There seems to be an error with that expression.

rubenmarin

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)

)