topic Re: Row-based calculation where cell value is 0 in QlikView https://community.qlik.com/t5/QlikView/Row-based-calculation-where-cell-value-is-0/m-p/1302210#M405735 <HTML><HEAD></HEAD><BODY><P>Hi,</P><P></P><P>May be as in attached file</P><P></P><P>Regards,</P><P>Andrey</P></BODY></HTML> Thu, 08 Jun 2017 06:30:01 GMT ahaahaaha 2017-06-08T06:30:01Z Row-based calculation where cell value is 0 https://community.qlik.com/t5/QlikView/Row-based-calculation-where-cell-value-is-0/m-p/1302203#M405728 <HTML><HEAD></HEAD><BODY><P>Hello community,</P><P></P><P>my goal is to<STRONG> not include</STRONG> calculations for 0 or null values in the <STRONG>Planned</STRONG> column so that they won't be added in the (<STRONG>Act - Plan</STRONG>) column.</P><P>This needs to be row-based.</P><P>I cannot do this in the load script because the data comes from different tables.</P><P>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </P><TABLE class="data-table"><TBODY><TR><TH class="border-bottom">Material</TH><TH class="border-bottom">Actual</TH><TH class="border-bottom">Planned</TH><TH>(Act - Plan)</TH></TR><TR><TD><STRONG>Total</STRONG></TD><TD>10</TD><TD>4</TD><TD><STRONG style="font-size: 14pt;">4</STRONG></TD></TR><TR><TD>A</TD><TD>8</TD><TD>4</TD><TD>4</TD></TR><TR><TD>B</TD><TD><STRONG>2</STRONG></TD><TD><STRONG>0</STRONG></TD><TD><SPAN style="font-size: 14pt;"><STRONG>0</STRONG></SPAN></TD></TR></TBODY></TABLE><P></P><P></P><P>So the question is how to do this row-based calculation?</P><P></P><P>Thank you for your Input.</P></BODY></HTML> Fri, 02 Jun 2017 11:49:35 GMT https://community.qlik.com/t5/QlikView/Row-based-calculation-where-cell-value-is-0/m-p/1302203#M405728 2017-06-02T11:49:35Z Re: Row-based calculation where cell value is 0 https://community.qlik.com/t5/QlikView/Row-based-calculation-where-cell-value-is-0/m-p/1302204#M405729 <HTML><HEAD></HEAD><BODY><P>=if(Plan&gt;0,Act-Plan,0)</P></BODY></HTML> Fri, 02 Jun 2017 11:54:52 GMT https://community.qlik.com/t5/QlikView/Row-based-calculation-where-cell-value-is-0/m-p/1302204#M405729 m_woolf 2017-06-02T11:54:52Z Re: Row-based calculation where cell value is 0 https://community.qlik.com/t5/QlikView/Row-based-calculation-where-cell-value-is-0/m-p/1302205#M405730 <HTML><HEAD></HEAD><BODY><P>like below !</P><P></P><P>=if(Planned&gt;0,Actual-Planned,0)</P></BODY></HTML> Fri, 02 Jun 2017 11:57:02 GMT https://community.qlik.com/t5/QlikView/Row-based-calculation-where-cell-value-is-0/m-p/1302205#M405730 vinieme12 2017-06-02T11:57:02Z Re: Row-based calculation where cell value is 0 https://community.qlik.com/t5/QlikView/Row-based-calculation-where-cell-value-is-0/m-p/1302206#M405731 <HTML><HEAD></HEAD><BODY><P>Ok, I do understand my own problem better now <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P><P>In my calculation I use sum functions for all three calculations. When I use above formulas I get the right calculation for each row but the total field is 0.</P><P>I tried different modifications of the formula but all resulted in a total value of 0.</P><P>For example:</P><P>=if(Planned&gt;0,sum(Actual)- sum(Planned),0)</P><P></P><P>Is tere a way to fix this?</P></BODY></HTML> Tue, 06 Jun 2017 11:39:59 GMT https://community.qlik.com/t5/QlikView/Row-based-calculation-where-cell-value-is-0/m-p/1302206#M405731 2017-06-06T11:39:59Z Re: Row-based calculation where cell value is 0 https://community.qlik.com/t5/QlikView/Row-based-calculation-where-cell-value-is-0/m-p/1302207#M405732 <HTML><HEAD></HEAD><BODY><P><EM>Go to expression &gt; Total &gt; select the Row&gt;total option that should help you to get the Total </EM></P></BODY></HTML> Tue, 06 Jun 2017 11:43:54 GMT https://community.qlik.com/t5/QlikView/Row-based-calculation-where-cell-value-is-0/m-p/1302207#M405732 avinashelite 2017-06-06T11:43:54Z Re: Row-based calculation where cell value is 0 https://community.qlik.com/t5/QlikView/Row-based-calculation-where-cell-value-is-0/m-p/1302208#M405733 <HTML><HEAD></HEAD><BODY><P>please post a sample app</P><P></P><P><A href="https://community.qlik.com/docs/DOC-1290">Preparing examples for Upload - Reduction and Data Scrambling</A></P></BODY></HTML> Tue, 06 Jun 2017 13:57:23 GMT https://community.qlik.com/t5/QlikView/Row-based-calculation-where-cell-value-is-0/m-p/1302208#M405733 vinieme12 2017-06-06T13:57:23Z Re: Row-based calculation where cell value is 0 https://community.qlik.com/t5/QlikView/Row-based-calculation-where-cell-value-is-0/m-p/1302209#M405734 <HTML><HEAD></HEAD><BODY><P>You are right, it works for a straight table to set the Total Mode to "Sum".</P><P>Is there a way to do this for a Pivot table as well?</P></BODY></HTML> Thu, 08 Jun 2017 06:06:10 GMT https://community.qlik.com/t5/QlikView/Row-based-calculation-where-cell-value-is-0/m-p/1302209#M405734 2017-06-08T06:06:10Z Re: Row-based calculation where cell value is 0 https://community.qlik.com/t5/QlikView/Row-based-calculation-where-cell-value-is-0/m-p/1302210#M405735 <HTML><HEAD></HEAD><BODY><P>Hi,</P><P></P><P>May be as in attached file</P><P></P><P>Regards,</P><P>Andrey</P></BODY></HTML> Thu, 08 Jun 2017 06:30:01 GMT https://community.qlik.com/t5/QlikView/Row-based-calculation-where-cell-value-is-0/m-p/1302210#M405735 ahaahaaha 2017-06-08T06:30:01Z Re: Row-based calculation where cell value is 0 https://community.qlik.com/t5/QlikView/Row-based-calculation-where-cell-value-is-0/m-p/1302211#M405736 <HTML><HEAD></HEAD><BODY><P><EM>Did you tried with the partial sum option in the presentation tab ?&nbsp; </EM></P></BODY></HTML> Thu, 08 Jun 2017 09:35:56 GMT https://community.qlik.com/t5/QlikView/Row-based-calculation-where-cell-value-is-0/m-p/1302211#M405736 avinashelite 2017-06-08T09:35:56Z