topic Re: complex sum in QlikView https://community.qlik.com/t5/QlikView/complex-sum/m-p/378911#M141341 <HTML><HEAD></HEAD><BODY><P> AGRR( sum( DISTINCT BILL AMOUNT) , <SPAN style="color: #636363; font-family: Arial; font-size: 12px; background-color: #eef4f9;">BILL NAME </SPAN>)</P></BODY></HTML> Fri, 17 Aug 2012 19:09:59 GMT Anonymous 2012-08-17T19:09:59Z complex sum https://community.qlik.com/t5/QlikView/complex-sum/m-p/378910#M141340 <HTML><HEAD></HEAD><BODY><P>I have a really complex problem. I need an expression which will enable me to monitor/supervise my buyers' payments, and how much they still owe me. Data base gives me these results in Excel table:</P><P></P><P>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Excel report sheet</P><P>BILL NAME&nbsp;&nbsp;&nbsp;&nbsp; BILL AMOUNT&nbsp;&nbsp;&nbsp;&nbsp; PAID</P><P>Bill1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $100.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $70.000</P><P>Bill1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $100.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $20.000</P><P>Bill1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $100.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $10.000</P><P>Bill2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $50.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $10.000</P><P>Bill2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $50.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $20.000</P><P></P><P>Bill 1 is $100.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Buyer owes $20.000</P><P>Bill 2 is $50.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Paid: $130.000</P><P></P><P></P><P>If I summarize the column: " Bill Amount", it would turn out that the buyer bought $400.000 of product, but i don't need that. I need it to add Bill 1 - $100.000, and bill2 -$50.000 only once, although it can appear more than one time in the table. </P><P>I know this is kinda complicated (messy), but I'm condemned to that report in Excel.</P><P></P><P>Thanks for your time <IMG src="https://community.qlik.com/legacyfs/online/emoticons/grin.png" /></P></BODY></HTML> Fri, 17 Aug 2012 18:11:12 GMT https://community.qlik.com/t5/QlikView/complex-sum/m-p/378910#M141340 2012-08-17T18:11:12Z Re: complex sum https://community.qlik.com/t5/QlikView/complex-sum/m-p/378911#M141341 <HTML><HEAD></HEAD><BODY><P> AGRR( sum( DISTINCT BILL AMOUNT) , <SPAN style="color: #636363; font-family: Arial; font-size: 12px; background-color: #eef4f9;">BILL NAME </SPAN>)</P></BODY></HTML> Fri, 17 Aug 2012 19:09:59 GMT https://community.qlik.com/t5/QlikView/complex-sum/m-p/378911#M141341 Anonymous 2012-08-17T19:09:59Z Re: complex sum https://community.qlik.com/t5/QlikView/complex-sum/m-p/378912#M141342 <HTML><HEAD></HEAD><BODY><P>Aggr(Sum(DISTINCT <A href="BILL AMOUNT"></A>),[BILL NAME])-Aggr(Sum(PAID), <A href="BILL NAME"></A>).</P><P></P><P>This shall give amount owed against each bill.</P><P></P><P>Regards,</P><P>K</P><P>On Aug 17, 2012 11:41 PM, "matoliki" &lt;qliktech@sgaur.hosted.jivesoftware.com&gt;</P></BODY></HTML> Fri, 17 Aug 2012 19:43:25 GMT https://community.qlik.com/t5/QlikView/complex-sum/m-p/378912#M141342 2012-08-17T19:43:25Z