topic Expression on interest calculation in QlikView

topic Expression on interest calculation in QlikView https://community.qlik.com/t5/QlikView/Expression-on-interest-calculation/m-p/1163500#M893570 <HTML><HEAD></HEAD><BODY><P>Dear Experts,</P><P>I have following information in my tables.</P><P></P><P>      <STRONG>LOAN REGISTER</STRONG>  </P><TABLE border="0" cellpadding="0" cellspacing="0" width="737"><TBODY><TR><TD height="20" width="96">START_DATE</TD><TD width="105">LOAN_NO</TD><TD width="81">PRO_CODE</TD><TD class="xl63" width="92"> NAME </TD><TD class="xl63" width="93"> LOAN_AMT </TD><TD width="73">STATUS</TD><TD width="92">SET_DATE</TD></TR><TR><TD align="right" class="xl64" height="20">01-Jan-16</TD><TD>RL0001</TD><TD>L01</TD><TD class="xl63"> AAAAAAAA </TD><TD class="xl63">   1,000,000.00 </TD><TD>ACTIVE</TD></TR><TR><TD align="right" class="xl64" height="20">01-Jan-16</TD><TD>RL0002</TD><TD>L02</TD><TD class="xl63"> BBBBBBBBB </TD><TD class="xl63">       500,000.00 </TD><TD>ACTIVE</TD></TR><TR><TD align="right" class="xl64" height="20">01-Jan-16</TD><TD>RL0003</TD><TD>L03</TD><TD class="xl63"> CCCCCCCCC </TD><TD class="xl63">   1,200,000.00 </TD><TD>ACTIVE</TD></TR><TR><TD align="right" class="xl64" height="20">01-Jan-16</TD><TD>RL0004</TD><TD>L03</TD><TD class="xl63"> DDDDDDDD </TD><TD class="xl63">       700,000.00 </TD><TD>ACTIVE</TD></TR><TR><TD align="right" class="xl64" height="20">01-Jan-16</TD><TD>RL0005</TD><TD>L02</TD><TD class="xl63"> EEEEEEEEEE </TD><TD class="xl63">       900,000.00 </TD><TD>ACTIVE</TD></TR><TR><TD align="right" class="xl64" height="20">01-Jan-16</TD><TD>RL0006</TD><TD>L01</TD><TD class="xl63"> FFFFFFFFFF </TD><TD class="xl63">       500,000.00 </TD><TD>ACTIVE</TD></TR><TR><TD align="right" class="xl64" height="20">02-Jan-16</TD><TD>RL0007</TD><TD>L02</TD><TD class="xl63"> GGGGGGGG </TD><TD class="xl63">   1,000,000.00 </TD><TD>ACTIVE</TD></TR><TR><TD align="right" class="xl64" height="20">02-Jan-16</TD><TD>RL0008</TD><TD>L02</TD><TD class="xl63"> HHHHHHHH </TD><TD class="xl63">   1,500,000.00 </TD><TD>ACTIVE</TD></TR><TR><TD align="right" class="xl64" height="20">02-Jan-16</TD><TD>RL0009</TD><TD>L01</TD><TD class="xl63"> IIIIIIIIIIIIIIIIII </TD><TD class="xl63">       100,000.00 </TD><TD>ACTIVE</TD></TR><TR><TD align="right" class="xl64" height="20">02-Jan-16</TD><TD>RL0010</TD><TD>L03</TD><TD class="xl63"> JJJJJJJJJJJJJJ </TD><TD class="xl63">       250,000.00 </TD><TD><P>ACTIVE</P></TD></TR></TBODY></TABLE><P></P><P>   <STRONG>PRODUCT MASTER FILE</STRONG></P><TABLE border="0" cellpadding="0" cellspacing="0" style="width: 201px;"><TBODY><TR><TD height="20" width="96">PRO_CODE</TD><TD width="105">RATE</TD></TR><TR><TD height="20">L01</TD><TD align="right" class="xl65">5%</TD></TR><TR><TD height="20">L02</TD><TD align="right" class="xl65">6%</TD></TR><TR><TD height="20">L03</TD><TD align="right" class="xl65">7%</TD></TR></TBODY></TABLE><P></P><P>   <STRONG>COLLECTOR MASTER FILE</STRONG></P><TABLE border="0" cellpadding="0" cellspacing="0" width="201"><TBODY><TR><TD height="20" width="96">COLL_CODE</TD><TD width="105">COL_NAME</TD></TR><TR><TD height="20">CL001</TD><TD>KKKKKKKKKKKK</TD></TR><TR><TD height="20">CL002</TD><TD>LLLLLLLLLLLLLLL</TD></TR><TR><TD height="20">CL003</TD><TD>MMMMMMMM</TD></TR><TR><TD height="20">MG01</TD><TD>NNNNNNNNN</TD></TR></TBODY></TABLE><P></P><P>     <STRONG>RECEIPT CASH BOOK</STRONG></P><TABLE border="0" cellpadding="0" cellspacing="0" style="width: 479px;"><TBODY><TR><TD height="20" width="96">REC_DATE</TD><TD width="105">REC_NO</TD><TD width="105">LOAN_NO</TD><TD width="81">COLL_CODE</TD><TD class="xl65" width="92"> AMOUNT </TD></TR><TR><TD align="right" class="xl66" height="20">1/02/2016</TD><TD>R0001</TD><TD>RL0001</TD><TD>CL001</TD><TD class="xl65">         50,000.00 </TD></TR><TR><TD align="right" class="xl66" height="20">1/02/2016</TD><TD>R0002</TD><TD>RL0002</TD><TD>CL001</TD><TD class="xl65">         25,000.00 </TD></TR><TR><TD align="right" class="xl66" height="20">1/02/2016</TD><TD>R0003</TD><TD>RL0003</TD><TD>CL002</TD><TD class="xl65">         60,000.00 </TD></TR><TR><TD align="right" class="xl66" height="20">1/02/2016</TD><TD>R0004</TD><TD>RL0004</TD><TD>CL002</TD><TD class="xl65">         35,000.00 </TD></TR><TR><TD align="right" class="xl66" height="20">1/02/2016</TD><TD>R0005</TD><TD>RL0005</TD><TD>CL003</TD><TD class="xl65">         45,000.00 </TD></TR><TR><TD align="right" class="xl66" height="20">1/02/2016</TD><TD>R0006</TD><TD>RL0006</TD><TD>CL002</TD><TD class="xl65">         25,000.00 </TD></TR><TR><TD align="right" class="xl66" height="20">2/02/2016</TD><TD>R0007</TD><TD>RL0007</TD><TD>CL001</TD><TD class="xl65">         50,000.00 </TD></TR><TR><TD align="right" class="xl66" height="20">2/02/2016</TD><TD>R0008</TD><TD>RL0008</TD><TD>MG01</TD><TD class="xl65">         75,000.00 </TD></TR><TR><TD align="right" class="xl66" height="20">2/02/2016</TD><TD>R0009</TD><TD>RL0009</TD><TD>MG01</TD><TD class="xl65">         10,000.00 </TD></TR><TR><TD align="right" class="xl66" height="20">2/02/2016</TD><TD>R0010</TD><TD>RL0010</TD><TD>MG01</TD><TD class="xl65">         12,500.00 </TD></TR></TBODY></TABLE><P> I want to calculate, 01. Daily due balance with accumulated interest.(daily basis)</P><P>                              <SPAN style="font-size: 13.3333px;">02. The capital recovery and the interest recovery from the above details.</SPAN></P><P>                              03.  How can i exclude the Saturdays from the above calculation,</P><P>                              04. If i want to exclude a specific Date from above calculation how can i do that,  </P><P>                              05. How can i use a calendar for this purpose.</P><P></P><P>thkx & rgds,</P><P>Priyantha.</P></BODY></HTML> Wed, 25 Nov 2020 16:16:04 GMT 2020-11-25T16:16:04Z Expression on interest calculation https://community.qlik.com/t5/QlikView/Expression-on-interest-calculation/m-p/1163500#M893570 <HTML><HEAD></HEAD><BODY><P>Dear Experts,</P><P>I have following information in my tables.</P><P></P><P>      <STRONG>LOAN REGISTER</STRONG>  </P><TABLE border="0" cellpadding="0" cellspacing="0" width="737"><TBODY><TR><TD height="20" width="96">START_DATE</TD><TD width="105">LOAN_NO</TD><TD width="81">PRO_CODE</TD><TD class="xl63" width="92"> NAME </TD><TD class="xl63" width="93"> LOAN_AMT </TD><TD width="73">STATUS</TD><TD width="92">SET_DATE</TD></TR><TR><TD align="right" class="xl64" height="20">01-Jan-16</TD><TD>RL0001</TD><TD>L01</TD><TD class="xl63"> AAAAAAAA </TD><TD class="xl63">   1,000,000.00 </TD><TD>ACTIVE</TD></TR><TR><TD align="right" class="xl64" height="20">01-Jan-16</TD><TD>RL0002</TD><TD>L02</TD><TD class="xl63"> BBBBBBBBB </TD><TD class="xl63">       500,000.00 </TD><TD>ACTIVE</TD></TR><TR><TD align="right" class="xl64" height="20">01-Jan-16</TD><TD>RL0003</TD><TD>L03</TD><TD class="xl63"> CCCCCCCCC </TD><TD class="xl63">   1,200,000.00 </TD><TD>ACTIVE</TD></TR><TR><TD align="right" class="xl64" height="20">01-Jan-16</TD><TD>RL0004</TD><TD>L03</TD><TD class="xl63"> DDDDDDDD </TD><TD class="xl63">       700,000.00 </TD><TD>ACTIVE</TD></TR><TR><TD align="right" class="xl64" height="20">01-Jan-16</TD><TD>RL0005</TD><TD>L02</TD><TD class="xl63"> EEEEEEEEEE </TD><TD class="xl63">       900,000.00 </TD><TD>ACTIVE</TD></TR><TR><TD align="right" class="xl64" height="20">01-Jan-16</TD><TD>RL0006</TD><TD>L01</TD><TD class="xl63"> FFFFFFFFFF </TD><TD class="xl63">       500,000.00 </TD><TD>ACTIVE</TD></TR><TR><TD align="right" class="xl64" height="20">02-Jan-16</TD><TD>RL0007</TD><TD>L02</TD><TD class="xl63"> GGGGGGGG </TD><TD class="xl63">   1,000,000.00 </TD><TD>ACTIVE</TD></TR><TR><TD align="right" class="xl64" height="20">02-Jan-16</TD><TD>RL0008</TD><TD>L02</TD><TD class="xl63"> HHHHHHHH </TD><TD class="xl63">   1,500,000.00 </TD><TD>ACTIVE</TD></TR><TR><TD align="right" class="xl64" height="20">02-Jan-16</TD><TD>RL0009</TD><TD>L01</TD><TD class="xl63"> IIIIIIIIIIIIIIIIII </TD><TD class="xl63">       100,000.00 </TD><TD>ACTIVE</TD></TR><TR><TD align="right" class="xl64" height="20">02-Jan-16</TD><TD>RL0010</TD><TD>L03</TD><TD class="xl63"> JJJJJJJJJJJJJJ </TD><TD class="xl63">       250,000.00 </TD><TD><P>ACTIVE</P></TD></TR></TBODY></TABLE><P></P><P>   <STRONG>PRODUCT MASTER FILE</STRONG></P><TABLE border="0" cellpadding="0" cellspacing="0" style="width: 201px;"><TBODY><TR><TD height="20" width="96">PRO_CODE</TD><TD width="105">RATE</TD></TR><TR><TD height="20">L01</TD><TD align="right" class="xl65">5%</TD></TR><TR><TD height="20">L02</TD><TD align="right" class="xl65">6%</TD></TR><TR><TD height="20">L03</TD><TD align="right" class="xl65">7%</TD></TR></TBODY></TABLE><P></P><P>   <STRONG>COLLECTOR MASTER FILE</STRONG></P><TABLE border="0" cellpadding="0" cellspacing="0" width="201"><TBODY><TR><TD height="20" width="96">COLL_CODE</TD><TD width="105">COL_NAME</TD></TR><TR><TD height="20">CL001</TD><TD>KKKKKKKKKKKK</TD></TR><TR><TD height="20">CL002</TD><TD>LLLLLLLLLLLLLLL</TD></TR><TR><TD height="20">CL003</TD><TD>MMMMMMMM</TD></TR><TR><TD height="20">MG01</TD><TD>NNNNNNNNN</TD></TR></TBODY></TABLE><P></P><P>     <STRONG>RECEIPT CASH BOOK</STRONG></P><TABLE border="0" cellpadding="0" cellspacing="0" style="width: 479px;"><TBODY><TR><TD height="20" width="96">REC_DATE</TD><TD width="105">REC_NO</TD><TD width="105">LOAN_NO</TD><TD width="81">COLL_CODE</TD><TD class="xl65" width="92"> AMOUNT </TD></TR><TR><TD align="right" class="xl66" height="20">1/02/2016</TD><TD>R0001</TD><TD>RL0001</TD><TD>CL001</TD><TD class="xl65">         50,000.00 </TD></TR><TR><TD align="right" class="xl66" height="20">1/02/2016</TD><TD>R0002</TD><TD>RL0002</TD><TD>CL001</TD><TD class="xl65">         25,000.00 </TD></TR><TR><TD align="right" class="xl66" height="20">1/02/2016</TD><TD>R0003</TD><TD>RL0003</TD><TD>CL002</TD><TD class="xl65">         60,000.00 </TD></TR><TR><TD align="right" class="xl66" height="20">1/02/2016</TD><TD>R0004</TD><TD>RL0004</TD><TD>CL002</TD><TD class="xl65">         35,000.00 </TD></TR><TR><TD align="right" class="xl66" height="20">1/02/2016</TD><TD>R0005</TD><TD>RL0005</TD><TD>CL003</TD><TD class="xl65">         45,000.00 </TD></TR><TR><TD align="right" class="xl66" height="20">1/02/2016</TD><TD>R0006</TD><TD>RL0006</TD><TD>CL002</TD><TD class="xl65">         25,000.00 </TD></TR><TR><TD align="right" class="xl66" height="20">2/02/2016</TD><TD>R0007</TD><TD>RL0007</TD><TD>CL001</TD><TD class="xl65">         50,000.00 </TD></TR><TR><TD align="right" class="xl66" height="20">2/02/2016</TD><TD>R0008</TD><TD>RL0008</TD><TD>MG01</TD><TD class="xl65">         75,000.00 </TD></TR><TR><TD align="right" class="xl66" height="20">2/02/2016</TD><TD>R0009</TD><TD>RL0009</TD><TD>MG01</TD><TD class="xl65">         10,000.00 </TD></TR><TR><TD align="right" class="xl66" height="20">2/02/2016</TD><TD>R0010</TD><TD>RL0010</TD><TD>MG01</TD><TD class="xl65">         12,500.00 </TD></TR></TBODY></TABLE><P> I want to calculate, 01. Daily due balance with accumulated interest.(daily basis)</P><P>                              <SPAN style="font-size: 13.3333px;">02. The capital recovery and the interest recovery from the above details.</SPAN></P><P>                              03.  How can i exclude the Saturdays from the above calculation,</P><P>                              04. If i want to exclude a specific Date from above calculation how can i do that,  </P><P>                              05. How can i use a calendar for this purpose.</P><P></P><P>thkx & rgds,</P><P>Priyantha.</P></BODY></HTML> Wed, 25 Nov 2020 16:16:04 GMT https://community.qlik.com/t5/QlikView/Expression-on-interest-calculation/m-p/1163500#M893570 2020-11-25T16:16:04Z