topic Re: Rangeavg() inside if(), unable to avoid null rows&columns in QlikView https://community.qlik.com/t5/QlikView/Rangeavg-inside-if-unable-to-avoid-null-rows-columns/m-p/786602#M667970 <HTML><HEAD></HEAD><BODY><P>try suppress missing values</P><P>With Regards</P><P>Harshita Gaur</P></BODY></HTML> Thu, 20 Nov 2014 11:33:49 GMT 2014-11-20T11:33:49Z Rangeavg() inside if(), unable to avoid null rows&columns https://community.qlik.com/t5/QlikView/Rangeavg-inside-if-unable-to-avoid-null-rows-columns/m-p/786601#M667969 <HTML><HEAD></HEAD><BODY><UL><LI>I tried the expression below and the result I got is show in the image(1) below.</LI></UL><H6></H6><H6>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =sum({&lt;Type={'Inventory'}&gt;} Units)</H6><P>Note: I'm using QV10</P><P></P><P>&nbsp; image(1)</P><P><IMG __jive_id="71951" alt="image01.JPG.jpg" class="jive-image" src="https://community.qlik.com/legacyfs/online/71951_image01.JPG.jpg" style="width: 620px; height: 97px;" /></P><UL><LI>Thereafter I modified the expression with "rangeavg()" function. But I get additional null rows&amp;columns (highlighted in the image below)</LI><LI>Also I tried suppress null values, but still I'm getting the same result.</LI></UL><P></P><H6 style="padding-left: 60px;">=if(sum({&lt;Type={'Inventory'}&gt;} Units),</H6><H6 style="padding-left: 60px;">&nbsp;&nbsp;&nbsp; sum({&lt;Type={'Inventory'}&gt;} Units)/</H6><H6 style="padding-left: 60px;">&nbsp;&nbsp;&nbsp; rangeavg(before(if(sum({&lt;Type={'SalesOut'}&gt;} Units)&gt;=0,sum({&lt;Type={'SalesOut'}&gt;} Units)),0,8))</H6><H6 style="padding-left: 60px;">&nbsp;&nbsp;&nbsp; )</H6><P><IMG __jive_id="71952" alt="image02.JPG.jpg" class="jive-image" height="112" src="https://community.qlik.com/legacyfs/online/71952_image02.JPG.jpg" style="width: 925.714px; height: 112px;" width="926" /></P><P>Could anyone please help me to solve this problem?</P><P>Thanks in advance</P><P></P><P>Message was edited by: gunenthiran b</P></BODY></HTML> Thu, 20 Nov 2014 09:49:57 GMT https://community.qlik.com/t5/QlikView/Rangeavg-inside-if-unable-to-avoid-null-rows-columns/m-p/786601#M667969 2014-11-20T09:49:57Z Re: Rangeavg() inside if(), unable to avoid null rows&columns https://community.qlik.com/t5/QlikView/Rangeavg-inside-if-unable-to-avoid-null-rows-columns/m-p/786602#M667970 <HTML><HEAD></HEAD><BODY><P>try suppress missing values</P><P>With Regards</P><P>Harshita Gaur</P></BODY></HTML> Thu, 20 Nov 2014 11:33:49 GMT https://community.qlik.com/t5/QlikView/Rangeavg-inside-if-unable-to-avoid-null-rows-columns/m-p/786602#M667970 2014-11-20T11:33:49Z Re: Rangeavg() inside if(), unable to avoid null rows&columns https://community.qlik.com/t5/QlikView/Rangeavg-inside-if-unable-to-avoid-null-rows-columns/m-p/786603#M667971 <HTML><HEAD></HEAD><BODY><P>Thank you for your reply.</P><P>But i tried suppress missing/null values also not working.</P></BODY></HTML> Fri, 21 Nov 2014 06:00:37 GMT https://community.qlik.com/t5/QlikView/Rangeavg-inside-if-unable-to-avoid-null-rows-columns/m-p/786603#M667971 2014-11-21T06:00:37Z Re: Rangeavg() inside if(), unable to avoid null rows&columns https://community.qlik.com/t5/QlikView/Rangeavg-inside-if-unable-to-avoid-null-rows-columns/m-p/786604#M667972 <HTML><HEAD></HEAD><BODY><P>hi,</P><P>can you attach your QVW? and <SPAN style="font-size: 10pt; line-height: 1.5em;"> your data sheet? </SPAN></P><P></P><P>With regards</P><P>Harshita gaur</P></BODY></HTML> Fri, 21 Nov 2014 06:20:13 GMT https://community.qlik.com/t5/QlikView/Rangeavg-inside-if-unable-to-avoid-null-rows-columns/m-p/786604#M667972 2014-11-21T06:20:13Z Re: Rangeavg() inside if(), unable to avoid null rows&columns https://community.qlik.com/t5/QlikView/Rangeavg-inside-if-unable-to-avoid-null-rows-columns/m-p/786605#M667973 <HTML><HEAD></HEAD><BODY><P>Hi Harshita gaur,<BR />I attached my qvw and data sheet in the question.<BR />please find the attachment and please note that we are using QV10.<BR />Thanks</P></BODY></HTML> Mon, 24 Nov 2014 12:06:22 GMT https://community.qlik.com/t5/QlikView/Rangeavg-inside-if-unable-to-avoid-null-rows-columns/m-p/786605#M667973 2014-11-24T12:06:22Z