topic Re: Set analysis to exclude zero in average value in QlikView

Set analysis to exclude zero in average value

Fri, 07 Aug 2015 08:57:47 GMT

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.

Re: Set analysis to exclude zero in average value

jonathandienst — Fri, 07 Aug 2015 08:59:48 GMT

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.

Re: Set analysis to exclude zero in average value

sunny_talwar — Fri, 07 Aug 2015 09:04:25 GMT

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

sunny_talwar — Fri, 07 Aug 2015 09:06:41 GMT

Alternatively you can also try this:

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

Re: Set analysis to exclude zero in average value

sasiparupudi1 — Fri, 07 Aug 2015 09:09:19 GMT

This?

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

Re: Set analysis to exclude zero in average value

Anonymous — Fri, 07 Aug 2015 09:12:42 GMT

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

Re: Set analysis to exclude zero in average value

Fri, 07 Aug 2015 10:48:32 GMT

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.

Re: Set analysis to exclude zero in average value

sasiparupudi1 — Fri, 07 Aug 2015 11:04:12 GMT

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)

Re: Set analysis to exclude zero in average value

Fri, 07 Aug 2015 11:15:45 GMT

There seems to be an error with that expression.

Re: Set analysis to exclude zero in average value

rubenmarin — Fri, 07 Aug 2015 11:53:31 GMT

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)

)

Re: Set analysis to exclude zero in average value

Fri, 07 Aug 2015 14:44:23 GMT

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,

Re: Set analysis to exclude zero in average value

rubenmarin — Fri, 07 Aug 2015 16:08:31 GMT

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

Re: Set analysis to exclude zero in average value

Wed, 12 Aug 2015 09:49:07 GMT

Ruben,

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