topic Re: Make average ignore NULL, BLANK and hyphen fields in QlikView https://community.qlik.com/t5/QlikView/Make-average-ignore-NULL-BLANK-and-hyphen-fields/m-p/1148411#M898021 <HTML><HEAD></HEAD><BODY><P>yes avg ignore those troublemakers by default.</P></BODY></HTML> Tue, 26 Jul 2016 16:08:05 GMT Anonymous 2016-07-26T16:08:05Z Make average ignore NULL, BLANK and hyphen fields https://community.qlik.com/t5/QlikView/Make-average-ignore-NULL-BLANK-and-hyphen-fields/m-p/1148408#M898018 <HTML><HEAD></HEAD><BODY><P>How can I make average (avg) function to ignore NULL, BLANK and hyphen ( - ) fields? I only want average to take into account valid numeric values. At the same time, I want to make the average of certain fields complaining a criteria (Order Type='Pending').</P><P></P><P>=avg({$&lt;[Order Type] = {"Pending'}&gt;} if(isNumeric([Hours]), [Hours])))</P><P></P><P>it will be equivalent to:</P><P></P><P>=sum({$&lt;[Order Type] = {'Pending'}&gt;}[Hours])/count({$&lt;[Order Type] = {'Pending'}&gt;}if(IsNum([Hours]),[Hours]))</P><P></P><P>Is it correct?</P></BODY></HTML> Wed, 25 Nov 2020 16:16:04 GMT https://community.qlik.com/t5/QlikView/Make-average-ignore-NULL-BLANK-and-hyphen-fields/m-p/1148408#M898018 2020-11-25T16:16:04Z Re: Make average ignore NULL, BLANK and hyphen fields https://community.qlik.com/t5/QlikView/Make-average-ignore-NULL-BLANK-and-hyphen-fields/m-p/1148409#M898019 <HTML><HEAD></HEAD><BODY><P>do you try this?</P><P></P><P><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">=avg({$&lt;[Order Type] = {"Pending'}&gt;} <SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">[Hours])</SPAN></SPAN></P></BODY></HTML> Tue, 26 Jul 2016 15:34:25 GMT https://community.qlik.com/t5/QlikView/Make-average-ignore-NULL-BLANK-and-hyphen-fields/m-p/1148409#M898019 2016-07-26T15:34:25Z Re: Make average ignore NULL, BLANK and hyphen fields https://community.qlik.com/t5/QlikView/Make-average-ignore-NULL-BLANK-and-hyphen-fields/m-p/1148410#M898020 <HTML><HEAD></HEAD><BODY><P>Does avg function ignore by default Null, blanks and hyphen fields as sum function does? I mean, I suppose avg function is not taken into account the nulls, blanks and hyphen fields (non-numeric) when performing calculations. Am I right?</P></BODY></HTML> Tue, 26 Jul 2016 15:42:01 GMT https://community.qlik.com/t5/QlikView/Make-average-ignore-NULL-BLANK-and-hyphen-fields/m-p/1148410#M898020 2016-07-26T15:42:01Z Re: Make average ignore NULL, BLANK and hyphen fields https://community.qlik.com/t5/QlikView/Make-average-ignore-NULL-BLANK-and-hyphen-fields/m-p/1148411#M898021 <HTML><HEAD></HEAD><BODY><P>yes avg ignore those troublemakers by default.</P></BODY></HTML> Tue, 26 Jul 2016 16:08:05 GMT https://community.qlik.com/t5/QlikView/Make-average-ignore-NULL-BLANK-and-hyphen-fields/m-p/1148411#M898021 Anonymous 2016-07-26T16:08:05Z Re: Make average ignore NULL, BLANK and hyphen fields https://community.qlik.com/t5/QlikView/Make-average-ignore-NULL-BLANK-and-hyphen-fields/m-p/1148412#M898022 <HTML><HEAD></HEAD><BODY><P><SPAN lang="en">the avg function ignores everything that is not number</SPAN></P></BODY></HTML> Tue, 26 Jul 2016 17:19:06 GMT https://community.qlik.com/t5/QlikView/Make-average-ignore-NULL-BLANK-and-hyphen-fields/m-p/1148412#M898022 2016-07-26T17:19:06Z