topic Re: sumif in QlikView

topic Re: sumif in QlikView https://community.qlik.com/t5/QlikView/sumif/m-p/1406870#M796499 <HTML><HEAD></HEAD><BODY><P>Thank you</P></BODY></HTML> Thu, 14 Dec 2017 15:54:03 GMT Anonymous 2017-12-14T15:54:03Z sumif https://community.qlik.com/t5/QlikView/sumif/m-p/1406868#M796497 <HTML><HEAD></HEAD><BODY><P>Hi </P><P>I have these there table:</P><P>   </P><TABLE border="0" cellpadding="0" cellspacing="0" style="width: 144px;"><TBODY><TR><TD align="left" class="xl63" height="20" width="88">item number</TD><TD align="left" class="xl63" style="border-left: none;" width="56">per unit</TD></TR><TR><TD align="right" class="xl63" height="20" style="border-top: none;">100</TD><TD align="right" class="xl63" style="border-top: none; border-left: none;">90</TD></TR><TR><TD align="right" class="xl63" height="20" style="border-top: none;">200</TD><TD align="right" class="xl63" style="border-top: none; border-left: none;">40</TD></TR><TR><TD align="right" class="xl63" height="20" style="border-top: none;">300</TD><TD align="right" class="xl63" style="border-top: none; border-left: none;">30</TD></TR><TR><TD align="right" class="xl63" height="20" style="border-top: none;">500</TD><TD align="right" class="xl63" style="border-top: none; border-left: none;">20</TD></TR><TR><TD align="right" class="xl63" height="20" style="border-top: none;">400</TD><TD align="right" class="xl63" style="border-top: none; border-left: none;">50</TD></TR></TBODY></TABLE><P></P><P>    </P><TABLE border="0" cellpadding="0" cellspacing="0" width="220"><TBODY><TR><TD align="left" class="xl65" height="20" width="88">item number</TD><TD align="left" class="xl65" style="border-left: none;" width="68">Date</TD><TD align="left" class="xl65" style="border-left: none;" width="64">qty</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">100</TD><TD align="right" class="xl66" style="border-top: none; border-left: none;">5/20/2016</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">1000</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">200</TD><TD align="right" class="xl66" style="border-top: none; border-left: none;">6/20/2016</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2000</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">300</TD><TD align="right" class="xl66" style="border-top: none; border-left: none;">7/20/2016</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">3000</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">400</TD><TD align="right" class="xl66" style="border-top: none; border-left: none;">8/20/2016</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">4000</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">100</TD><TD align="right" class="xl66" style="border-top: none; border-left: none;">5/20/2017</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2000</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">200</TD><TD align="right" class="xl66" style="border-top: none; border-left: none;">6/20/2017</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">3000</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">300</TD><TD align="right" class="xl66" style="border-top: none; border-left: none;">7/20/2017</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">4000</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">400</TD><TD align="right" class="xl66" style="border-top: none; border-left: none;">8/20/2017</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">5000</TD></TR></TBODY></TABLE><P></P><P></P><P>   </P><TABLE border="0" cellpadding="0" cellspacing="0" width="196"><TBODY><TR><TD align="left" class="xl65" height="20" width="68">Date</TD><TD align="left" class="xl65" style="border-left: none;" width="64">year</TD><TD align="left" class="xl65" style="border-left: none;" width="64">month</TD></TR><TR><TD align="right" class="xl66" height="20" style="border-top: none;">5/20/2016</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2016</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">5</TD></TR><TR><TD align="right" class="xl66" height="20" style="border-top: none;">6/20/2016</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2016</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">6</TD></TR><TR><TD align="right" class="xl66" height="20" style="border-top: none;">7/20/2016</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2016</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">7</TD></TR><TR><TD align="right" class="xl66" height="20" style="border-top: none;">8/20/2016</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2016</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">8</TD></TR><TR><TD align="right" class="xl66" height="20" style="border-top: none;">5/20/2017</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2017</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">5</TD></TR><TR><TD align="right" class="xl66" height="20" style="border-top: none;">6/20/2017</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2017</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">6</TD></TR><TR><TD align="right" class="xl66" height="20" style="border-top: none;">7/20/2017</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2017</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">7</TD></TR><TR><TD align="right" class="xl66" height="20" style="border-top: none;">8/20/2017</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2017</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;"><P>8</P></TD></TR></TBODY></TABLE><P></P><P></P><P>How would I calculate the total Qty by per unit based on year?</P><P>the result should be like these:</P><P>   </P><TABLE border="0" cellpadding="0" cellspacing="0" width="176"><TBODY><TR><TD align="left" height="20" style="font-size: 11pt; color: white; font-weight: bold; font-family: Calibri; border-top: 1pt solid #366092; border-right: none; border-bottom: none; border-left: none; background: #4f81bd;" width="92">Row Labels</TD><TD align="left" class="xl66" style="font-size: 11pt; color: white; font-weight: bold; font-family: Calibri; border-top: 1pt solid #366092; border-right: none; border-bottom: none; border-left: none; background: #4f81bd;" width="84"> qty/per unit </TD></TR><TR><TD class="xl65" height="20" style="font-size: 11pt; color: black; font-family: Calibri;">2016</TD><TD align="left" class="xl66"> 241 </TD></TR><TR><TD class="xl65" height="20" style="font-size: 11pt; color: black; font-family: Calibri;">2017</TD><TD align="left" class="xl66"> 331 </TD></TR><TR><TD class="xl65" height="20" style="font-size: 11pt; color: black; font-weight: bold; font-family: Calibri; border-top: 0.5pt solid #366092; border-right: none; border-bottom: 1pt solid #366092; border-left: none;">Grand Total</TD><TD align="left" class="xl66" style="font-size: 11pt; color: black; font-weight: bold; font-family: Calibri; border-top: 0.5pt solid #366092; border-right: none; border-bottom: 1pt solid #366092; border-left: none;">                  572 </TD></TR></TBODY></TABLE><P></P><P>and the detail table is:</P><P>      </P><TABLE border="0" cellpadding="0" cellspacing="0" width="496"><TBODY><TR><TD class="xl65" height="20" width="88">item number</TD><TD class="xl65" style="border-left: none;" width="68">Date</TD><TD class="xl65" style="border-left: none;" width="64">qty</TD><TD class="xl65" style="border-left: none;" width="64">unit</TD><TD class="xl65" style="border-left: none;" width="84">Qty/per unit</TD><TD class="xl65" style="border-left: none;" width="64">year</TD><TD class="xl65" style="border-left: none;" width="64">month</TD></TR><TR><TD class="xl65" height="20" style="border-top: none;">100</TD><TD class="xl66" style="border-top: none; border-left: none;">5/20/2016</TD><TD class="xl65" style="border-top: none; border-left: none;">1000</TD><TD class="xl65" style="border-top: none; border-left: none;">90</TD><TD class="xl65" style="border-top: none; border-left: none;">11.11111111</TD><TD class="xl65" style="border-top: none; border-left: none;">2016</TD><TD class="xl65" style="border-top: none; border-left: none;">5</TD></TR><TR><TD class="xl65" height="20" style="border-top: none;">200</TD><TD class="xl66" style="border-top: none; border-left: none;">6/20/2016</TD><TD class="xl65" style="border-top: none; border-left: none;">2000</TD><TD class="xl65" style="border-top: none; border-left: none;">40</TD><TD class="xl65" style="border-top: none; border-left: none;">50</TD><TD class="xl65" style="border-top: none; border-left: none;">2016</TD><TD class="xl65" style="border-top: none; border-left: none;">6</TD></TR><TR><TD class="xl65" height="20" style="border-top: none;">300</TD><TD class="xl66" style="border-top: none; border-left: none;">7/20/2016</TD><TD class="xl65" style="border-top: none; border-left: none;">3000</TD><TD class="xl65" style="border-top: none; border-left: none;">30</TD><TD class="xl65" style="border-top: none; border-left: none;">100</TD><TD class="xl65" style="border-top: none; border-left: none;">2016</TD><TD class="xl65" style="border-top: none; border-left: none;">7</TD></TR><TR><TD class="xl65" height="20" style="border-top: none;">400</TD><TD class="xl66" style="border-top: none; border-left: none;">8/20/2016</TD><TD class="xl65" style="border-top: none; border-left: none;">4000</TD><TD class="xl65" style="border-top: none; border-left: none;">50</TD><TD class="xl65" style="border-top: none; border-left: none;">80</TD><TD class="xl65" style="border-top: none; border-left: none;">2016</TD><TD class="xl65" style="border-top: none; border-left: none;">8</TD></TR><TR><TD class="xl65" height="20" style="border-top: none;">100</TD><TD class="xl66" style="border-top: none; border-left: none;">5/20/2017</TD><TD class="xl65" style="border-top: none; border-left: none;">2000</TD><TD class="xl65" style="border-top: none; border-left: none;">90</TD><TD class="xl65" style="border-top: none; border-left: none;">22.22222222</TD><TD class="xl65" style="border-top: none; border-left: none;">2017</TD><TD class="xl65" style="border-top: none; border-left: none;">5</TD></TR><TR><TD class="xl65" height="20" style="border-top: none;">200</TD><TD class="xl66" style="border-top: none; border-left: none;">6/20/2017</TD><TD class="xl65" style="border-top: none; border-left: none;">3000</TD><TD class="xl65" style="border-top: none; border-left: none;">40</TD><TD class="xl65" style="border-top: none; border-left: none;">75</TD><TD class="xl65" style="border-top: none; border-left: none;">2017</TD><TD class="xl65" style="border-top: none; border-left: none;">6</TD></TR><TR><TD class="xl65" height="20" style="border-top: none;">300</TD><TD class="xl66" style="border-top: none; border-left: none;">7/20/2017</TD><TD class="xl65" style="border-top: none; border-left: none;">4000</TD><TD class="xl65" style="border-top: none; border-left: none;">30</TD><TD class="xl65" style="border-top: none; border-left: none;">133.3333333</TD><TD class="xl65" style="border-top: none; border-left: none;">2017</TD><TD class="xl65" style="border-top: none; border-left: none;">7</TD></TR><TR><TD class="xl65" height="20" style="border-top: none;">400</TD><TD class="xl66" style="border-top: none; border-left: none;">8/20/2017</TD><TD class="xl65" style="border-top: none; border-left: none;">5000</TD><TD class="xl65" style="border-top: none; border-left: none;">50</TD><TD class="xl65" style="border-top: none; border-left: none;">100</TD><TD class="xl65" style="border-top: none; border-left: none;">2017</TD><TD class="xl65" style="border-top: none; border-left: none;">8</TD></TR></TBODY></TABLE></BODY></HTML> Wed, 25 Nov 2020 16:16:04 GMT https://community.qlik.com/t5/QlikView/sumif/m-p/1406868#M796497 Anonymous 2020-11-25T16:16:04Z Re: sumif https://community.qlik.com/t5/QlikView/sumif/m-p/1406869#M796498 <HTML><HEAD></HEAD><BODY><P>Try this expression</P><P></P><P><STRONG>Sum(qty/[per unit])</STRONG></P><P></P><P><IMG alt="Capture.PNG" class="jive-image image-1" src="https://community.qlik.com/legacyfs/online/187015_Capture.PNG" style="height: 338px; width: 620px;" /></P></BODY></HTML> Thu, 14 Dec 2017 15:37:23 GMT https://community.qlik.com/t5/QlikView/sumif/m-p/1406869#M796498 sunny_talwar 2017-12-14T15:37:23Z Re: sumif https://community.qlik.com/t5/QlikView/sumif/m-p/1406870#M796499 <HTML><HEAD></HEAD><BODY><P>Thank you</P></BODY></HTML> Thu, 14 Dec 2017 15:54:03 GMT https://community.qlik.com/t5/QlikView/sumif/m-p/1406870#M796499 Anonymous 2017-12-14T15:54:03Z