Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
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

Re: Set analysis to exclude zero in average value

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

12 Replies
MVP
MVP

Re: Set analysis to exclude zero in average value

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

Re: Set analysis to exclude zero in average value

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

Re: Set analysis to exclude zero in average value

Alternatively you can also try this:

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

sasiparupudi1
Honored Contributor III

Re: Set analysis to exclude zero in average value

This?

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



lusaxy81
New Contributor III

Re: Set analysis to exclude zero in average value

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

Re: Set analysis to exclude zero in average value

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
Honored Contributor III

Re: Set analysis to exclude zero in average value

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

Re: Set analysis to exclude zero in average value

There seems to be an error with that expression.

Re: Set analysis to exclude zero in average value

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)

)