topic Make sum 0 when at least one element is null in QlikView https://community.qlik.com/t5/QlikView/Make-sum-0-when-at-least-one-element-is-null/m-p/294972#M580416 <HTML><HEAD></HEAD><BODY><P>Hi guys,</P><P></P><P>I have Product, Market dimensions and a table of values.</P><P>Market is divided into Markets and Market Areas. </P><P></P><P>What I want to do is to sum the data on Market Areas and return 0 each time at least one market is 0 or missing.</P><P></P><P>Example:</P><P></P><P><STRONG>Markets:</STRONG></P><P>Market&nbsp;&nbsp;&nbsp;&nbsp; MarketArea</P><P>UK&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EU</P><P>France&nbsp;&nbsp;&nbsp;&nbsp; EU</P><P>Italy&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EU</P><P>UK&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EU27</P><P>Italy&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EU27</P><P>Germany&nbsp; EU27</P><P></P><P><STRONG>Demand:</STRONG></P><P>Market&nbsp;&nbsp;&nbsp;&nbsp; Product&nbsp;&nbsp;&nbsp;&nbsp; Demand</P><P>UK&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Prod1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10</P><P>France&nbsp;&nbsp;&nbsp;&nbsp; Prod1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5</P><P>Italy&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Prod1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 11</P><P>UK&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Prod2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5</P><P></P><P>So, </P><P>1) the sum per market area should result in this:</P><P>MarketArea&nbsp;&nbsp;&nbsp;&nbsp; Demand</P><P>EU&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10+5+11+5</P><P>EU27&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 (since Germany is missing)</P><P></P><P>2) but the sum per market area per product should result in this:</P><P>MarketArea&nbsp;&nbsp;&nbsp;&nbsp; Product&nbsp;&nbsp;&nbsp;&nbsp; Demand</P><P>EU&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Prod1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10+5+11</P><P>EU&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Prod2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 (instead of 5 since Italy and France are missing)</P><P>EU27&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Prod1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10 + 11 </P><P>EU 27&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Prod2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 (instead of 5 since Italy and Germany are missing)</P><P></P><P>How can I do this?</P><P></P><P>Valera</P></BODY></HTML> Fri, 20 Jan 2012 17:21:40 GMT Anonymous 2012-01-20T17:21:40Z Make sum 0 when at least one element is null https://community.qlik.com/t5/QlikView/Make-sum-0-when-at-least-one-element-is-null/m-p/294972#M580416 <HTML><HEAD></HEAD><BODY><P>Hi guys,</P><P></P><P>I have Product, Market dimensions and a table of values.</P><P>Market is divided into Markets and Market Areas. </P><P></P><P>What I want to do is to sum the data on Market Areas and return 0 each time at least one market is 0 or missing.</P><P></P><P>Example:</P><P></P><P><STRONG>Markets:</STRONG></P><P>Market&nbsp;&nbsp;&nbsp;&nbsp; MarketArea</P><P>UK&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EU</P><P>France&nbsp;&nbsp;&nbsp;&nbsp; EU</P><P>Italy&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EU</P><P>UK&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EU27</P><P>Italy&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EU27</P><P>Germany&nbsp; EU27</P><P></P><P><STRONG>Demand:</STRONG></P><P>Market&nbsp;&nbsp;&nbsp;&nbsp; Product&nbsp;&nbsp;&nbsp;&nbsp; Demand</P><P>UK&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Prod1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10</P><P>France&nbsp;&nbsp;&nbsp;&nbsp; Prod1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5</P><P>Italy&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Prod1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 11</P><P>UK&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Prod2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5</P><P></P><P>So, </P><P>1) the sum per market area should result in this:</P><P>MarketArea&nbsp;&nbsp;&nbsp;&nbsp; Demand</P><P>EU&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10+5+11+5</P><P>EU27&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 (since Germany is missing)</P><P></P><P>2) but the sum per market area per product should result in this:</P><P>MarketArea&nbsp;&nbsp;&nbsp;&nbsp; Product&nbsp;&nbsp;&nbsp;&nbsp; Demand</P><P>EU&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Prod1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10+5+11</P><P>EU&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Prod2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 (instead of 5 since Italy and France are missing)</P><P>EU27&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Prod1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10 + 11 </P><P>EU 27&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Prod2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 (instead of 5 since Italy and Germany are missing)</P><P></P><P>How can I do this?</P><P></P><P>Valera</P></BODY></HTML> Fri, 20 Jan 2012 17:21:40 GMT https://community.qlik.com/t5/QlikView/Make-sum-0-when-at-least-one-element-is-null/m-p/294972#M580416 Anonymous 2012-01-20T17:21:40Z Make sum 0 when at least one element is null https://community.qlik.com/t5/QlikView/Make-sum-0-when-at-least-one-element-is-null/m-p/294973#M580417 <HTML><HEAD></HEAD><BODY><P>Correction:</P><P></P><P style="background-color: #eef4f9; font-size: 12px; color: #636363; font-family: Arial;">2) but the sum per market area per product should result in this:</P><P style="background-color: #eef4f9; font-size: 12px; color: #636363; font-family: Arial;">MarketArea&nbsp;&nbsp;&nbsp;&nbsp; Product&nbsp;&nbsp;&nbsp;&nbsp; Demand</P><P style="background-color: #eef4f9; font-size: 12px; color: #636363; font-family: Arial;">EU&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Prod1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10+5+11</P><P style="background-color: #eef4f9; font-size: 12px; color: #636363; font-family: Arial;">EU&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Prod2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 (instead of 5 since Italy and France are missing)</P><P style="background-color: #eef4f9; font-size: 12px; color: #636363; font-family: Arial;">EU27&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Prod1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 (since Germany is missing)</P><P style="background-color: #eef4f9; font-size: 12px; color: #636363; font-family: Arial;">EU 27&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Prod2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 (instead of 5 since Italy and Germany are missing)</P></BODY></HTML> Fri, 20 Jan 2012 17:23:44 GMT https://community.qlik.com/t5/QlikView/Make-sum-0-when-at-least-one-element-is-null/m-p/294973#M580417 Anonymous 2012-01-20T17:23:44Z