topic Problems with TOTAL in QlikView https://community.qlik.com/t5/QlikView/Problems-with-TOTAL/m-p/1120141#M463545 <HTML><HEAD></HEAD><BODY><P>Hello All,</P><P></P><P>I'm working on a pipeline revenue. Got the following tables</P><P></P><P>MasterCalendar</P><P>- Date</P><P>- Weeknr</P><P>- Year</P><P>- WeeknrYear</P><P></P><P>Projects</P><P>- Projectnr</P><P>- TotalAmount</P><P></P><P>Invoices Schedule</P><P>- Projectnr</P><P>- Date</P><P>- Invoicenr</P><P>- Amount</P><P>- Percentage</P><P></P><P>About the invoices Schedule the amount could be 0 with a percentage (50%) so the invoices will be TotalAmount * Percentage. At last every Project has an invoice with Amount 0 and Percentage 0 named ClosingInvoice. This is for the extra work. TotalAmount will be updated but now the invoices Schedule must calculate what the amount of the ClosingInvoice will be.</P><P></P><P>Example:</P><P>Projects</P><TABLE border="1" class="jiveBorder" style="border: 1px solid rgb(0, 0, 0); width: 100%;"><TBODY><TR><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle">Projects</TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>TotalAmount</STRONG></TH></TR><TR><TD style="padding: 2px;">X</TD><TD style="padding: 2px;">100.000</TD></TR><TR><TD style="padding: 2px;">Y</TD><TD style="padding: 2px;">50.000</TD></TR></TBODY></TABLE><P>Project X was 90.000 but has 10.000 extra work</P><P></P><P>Invoices Schedule</P><TABLE border="1" class="jiveBorder" style="border: 1px solid rgb(0, 0, 0); width: 100%;"><TBODY><TR><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>Projectnr</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>Date</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>Invoicenr</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>Amount</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>Percentage</STRONG></TH></TR><TR><TD style="padding: 2px;">X</TD><TD style="padding: 2px;">1-1</TD><TD style="padding: 2px;">1</TD><TD style="padding: 2px;">50.000</TD><TD style="padding: 2px;"></TD></TR><TR><TD style="padding: 2px;">X</TD><TD style="padding: 2px;">2-1</TD><TD style="padding: 2px;">2</TD><TD style="padding: 2px;">40.000</TD><TD style="padding: 2px;"></TD></TR><TR><TD style="padding: 2px;">X</TD><TD style="padding: 2px;">3-1</TD><TD style="padding: 2px;">3</TD><TD style="padding: 2px;">0</TD><TD style="padding: 2px;">0</TD></TR><TR><TD style="padding: 2px;">Y</TD><TD style="padding: 2px;">1-1</TD><TD style="padding: 2px;">4</TD><TD style="padding: 2px;"></TD><TD style="padding: 2px;">50%</TD></TR><TR><TD style="padding: 2px;">Y</TD><TD style="padding: 2px;">3-1</TD><TD style="padding: 2px;">5</TD><TD style="padding: 2px;">0</TD><TD style="padding: 2px;">0</TD></TR></TBODY></TABLE><P></P><P>The ClosingInvoice could be calculated with TotalAmount - amount already been invoiced. I use sum(TOTAL &lt;Projectnr&gt; Amount)</P><P></P><P>What I need is as follows</P><P></P><TABLE border="1" class="jiveBorder" style="border: 1px solid rgb(0, 0, 0); width: 100%;"><TBODY><TR><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>Date</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle">Revenue (correct)</TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle">Revenues (wrong)</TH></TR><TR><TD style="padding: 2px;">1-1</TD><TD style="padding: 2px;">75.000</TD><TD style="padding: 2px;">75.000</TD></TR><TR><TD style="padding: 2px;">2-1</TD><TD style="padding: 2px;">40.000</TD><TD style="padding: 2px;">40.000</TD></TR><TR><TD style="padding: 2px;">3-1</TD><TD style="padding: 2px;">35.000</TD><TD style="padding: 2px;">-80.000</TD></TR></TBODY></TABLE><P></P><P>3-1 Correct:</P><P>100.000 - (50.000 + 40.000)</P><P>50.000 - (25000)</P><P></P><P>3-1 Wrong:</P><P>100.000 - (50.000 + 40.000 + 25.000)</P><P>50.000 - <SPAN style="font-size: 13.3333px;">(50.000 + 40.000 + 25.000)</SPAN></P><P></P><P>When I select one contract everything is working fine</P></BODY></HTML> Mon, 27 Jun 2016 10:31:34 GMT tomhovens 2016-06-27T10:31:34Z Problems with TOTAL https://community.qlik.com/t5/QlikView/Problems-with-TOTAL/m-p/1120141#M463545 <HTML><HEAD></HEAD><BODY><P>Hello All,</P><P></P><P>I'm working on a pipeline revenue. Got the following tables</P><P></P><P>MasterCalendar</P><P>- Date</P><P>- Weeknr</P><P>- Year</P><P>- WeeknrYear</P><P></P><P>Projects</P><P>- Projectnr</P><P>- TotalAmount</P><P></P><P>Invoices Schedule</P><P>- Projectnr</P><P>- Date</P><P>- Invoicenr</P><P>- Amount</P><P>- Percentage</P><P></P><P>About the invoices Schedule the amount could be 0 with a percentage (50%) so the invoices will be TotalAmount * Percentage. At last every Project has an invoice with Amount 0 and Percentage 0 named ClosingInvoice. This is for the extra work. TotalAmount will be updated but now the invoices Schedule must calculate what the amount of the ClosingInvoice will be.</P><P></P><P>Example:</P><P>Projects</P><TABLE border="1" class="jiveBorder" style="border: 1px solid rgb(0, 0, 0); width: 100%;"><TBODY><TR><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle">Projects</TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>TotalAmount</STRONG></TH></TR><TR><TD style="padding: 2px;">X</TD><TD style="padding: 2px;">100.000</TD></TR><TR><TD style="padding: 2px;">Y</TD><TD style="padding: 2px;">50.000</TD></TR></TBODY></TABLE><P>Project X was 90.000 but has 10.000 extra work</P><P></P><P>Invoices Schedule</P><TABLE border="1" class="jiveBorder" style="border: 1px solid rgb(0, 0, 0); width: 100%;"><TBODY><TR><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>Projectnr</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>Date</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>Invoicenr</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>Amount</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>Percentage</STRONG></TH></TR><TR><TD style="padding: 2px;">X</TD><TD style="padding: 2px;">1-1</TD><TD style="padding: 2px;">1</TD><TD style="padding: 2px;">50.000</TD><TD style="padding: 2px;"></TD></TR><TR><TD style="padding: 2px;">X</TD><TD style="padding: 2px;">2-1</TD><TD style="padding: 2px;">2</TD><TD style="padding: 2px;">40.000</TD><TD style="padding: 2px;"></TD></TR><TR><TD style="padding: 2px;">X</TD><TD style="padding: 2px;">3-1</TD><TD style="padding: 2px;">3</TD><TD style="padding: 2px;">0</TD><TD style="padding: 2px;">0</TD></TR><TR><TD style="padding: 2px;">Y</TD><TD style="padding: 2px;">1-1</TD><TD style="padding: 2px;">4</TD><TD style="padding: 2px;"></TD><TD style="padding: 2px;">50%</TD></TR><TR><TD style="padding: 2px;">Y</TD><TD style="padding: 2px;">3-1</TD><TD style="padding: 2px;">5</TD><TD style="padding: 2px;">0</TD><TD style="padding: 2px;">0</TD></TR></TBODY></TABLE><P></P><P>The ClosingInvoice could be calculated with TotalAmount - amount already been invoiced. I use sum(TOTAL &lt;Projectnr&gt; Amount)</P><P></P><P>What I need is as follows</P><P></P><TABLE border="1" class="jiveBorder" style="border: 1px solid rgb(0, 0, 0); width: 100%;"><TBODY><TR><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>Date</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle">Revenue (correct)</TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle">Revenues (wrong)</TH></TR><TR><TD style="padding: 2px;">1-1</TD><TD style="padding: 2px;">75.000</TD><TD style="padding: 2px;">75.000</TD></TR><TR><TD style="padding: 2px;">2-1</TD><TD style="padding: 2px;">40.000</TD><TD style="padding: 2px;">40.000</TD></TR><TR><TD style="padding: 2px;">3-1</TD><TD style="padding: 2px;">35.000</TD><TD style="padding: 2px;">-80.000</TD></TR></TBODY></TABLE><P></P><P>3-1 Correct:</P><P>100.000 - (50.000 + 40.000)</P><P>50.000 - (25000)</P><P></P><P>3-1 Wrong:</P><P>100.000 - (50.000 + 40.000 + 25.000)</P><P>50.000 - <SPAN style="font-size: 13.3333px;">(50.000 + 40.000 + 25.000)</SPAN></P><P></P><P>When I select one contract everything is working fine</P></BODY></HTML> Mon, 27 Jun 2016 10:31:34 GMT https://community.qlik.com/t5/QlikView/Problems-with-TOTAL/m-p/1120141#M463545 tomhovens 2016-06-27T10:31:34Z Re: Problems with TOTAL https://community.qlik.com/t5/QlikView/Problems-with-TOTAL/m-p/1120142#M463546 <HTML><HEAD></HEAD><BODY><P>Hi,</P><P></P><P>Can you send me Sample app you worked?</P></BODY></HTML> Mon, 27 Jun 2016 11:14:44 GMT https://community.qlik.com/t5/QlikView/Problems-with-TOTAL/m-p/1120142#M463546 muthukumar77 2016-06-27T11:14:44Z Re: Problems with TOTAL https://community.qlik.com/t5/QlikView/Problems-with-TOTAL/m-p/1120143#M463547 <HTML><HEAD></HEAD><BODY><P>yes, i can</P></BODY></HTML> Mon, 27 Jun 2016 12:23:23 GMT https://community.qlik.com/t5/QlikView/Problems-with-TOTAL/m-p/1120143#M463547 tomhovens 2016-06-27T12:23:23Z Re: Problems with TOTAL https://community.qlik.com/t5/QlikView/Problems-with-TOTAL/m-p/1120144#M463548 <HTML><HEAD></HEAD><BODY><P>What is the logic of revenue wrong? couldn't understand the logic from the calculation mentioned <IMG src="https://community.qlik.com/legacyfs/online/emoticons/sad.png" /></P><P>I am using Personal edition, great if you can share the expressions.</P></BODY></HTML> Mon, 27 Jun 2016 14:17:39 GMT https://community.qlik.com/t5/QlikView/Problems-with-TOTAL/m-p/1120144#M463548 Digvijay_Singh 2016-06-27T14:17:39Z Re: Problems with TOTAL https://community.qlik.com/t5/QlikView/Problems-with-TOTAL/m-p/1120145#M463549 <HTML><HEAD></HEAD><BODY><P>Got already the correct calculation, but thanks for thinking with me</P></BODY></HTML> Mon, 27 Jun 2016 14:28:31 GMT https://community.qlik.com/t5/QlikView/Problems-with-TOTAL/m-p/1120145#M463549 tomhovens 2016-06-27T14:28:31Z