topic Pivot Table Sub Total in QlikView https://community.qlik.com/t5/QlikView/Pivot-Table-Sub-Total/m-p/293720#M1200941 <HTML><HEAD></HEAD><BODY><P>I have done this already in excel but I am in the process of trying to replicate this process in QV. I think I would use the aggregation command but I am not certain on this. The bottom two lines are NOT included in the data but they need to be included in the pivot(calculated dimension maybe?)</P><P>It goes like this .. sum(trust income ....other fee income) = total other income</P><P>Sum(total NII, Total other income ) = Total Revenue</P><P>I have this data linked in access using the following field names</P><P></P><TABLE border="0" cellpadding="0" cellspacing="0" style="width: 48pt; border-collapse: collapse;" width="64"><COLGROUP><COL style="width: 48pt;" width="64" /></COLGROUP><TBODY><TR height="20" style="height: 15pt;"><TD height="20" style="background-color: transparent; width: 48pt; height: 15pt; border: #ece9d8;" width="64">Year</TD></TR><TR height="22" style="height: 16.5pt;"><TD height="22" style="background-color: transparent; height: 16.5pt; border: #ece9d8;">Scenario</TD></TR><TR height="22" style="height: 16.5pt;"><TD height="22" style="background-color: transparent; height: 16.5pt; border: #ece9d8;">Month</TD></TR><TR height="22" style="height: 16.5pt;"><TD height="22" style="background-color: transparent; height: 16.5pt; border: #ece9d8;">Business Unit</TD></TR><TR height="22" style="height: 16.5pt;"><TD height="22" style="background-color: transparent; height: 16.5pt; border: #ece9d8;">Line</TD></TR><TR height="22" style="height: 16.5pt;"><TD height="22" style="background-color: transparent; height: 16.5pt; border: #ece9d8;">amount</TD></TR></TBODY></TABLE><BR /><BR /> <P>Any ideas appreciated!</P><P></P><TABLE border="0" cellpadding="0" cellspacing="0" style="width: 328pt; border-collapse: collapse;" width="435"><COLGROUP><COL style="width: 98pt; mso-width-source: userset; mso-width-alt: 4790; mso-outline-parent: collapsed;" width="131" /><COL style="width: 42pt; mso-width-source: userset; mso-width-alt: 2048; mso-outline-parent: collapsed;" width="56" /><COL style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267; mso-outline-parent: collapsed;" width="62" /><COL style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267; mso-outline-parent: collapsed;" width="62" /><COL style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267; mso-outline-parent: collapsed;" width="62" /><COL style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267; mso-outline-parent: collapsed;" width="62" /></COLGROUP><TBODY><TR height="17" style="height: 12.75pt;"><TD class="xl63" height="17" style="border-bottom: black 1pt solid; border-left: black 0.5pt solid; background-color: silver; width: 98pt; height: 12.75pt; border-top: black 0.5pt solid; border-right: black 1pt solid;" width="131"><STRONG></STRONG></TD><TD class="xl64" style="border-bottom: black 0.5pt solid; border-left: black; background-color: #e6e6e6; width: 42pt; border-top: black 0.5pt solid; border-right: black 0.5pt solid;" width="56"><STRONG>Month</STRONG></TD><TD class="xl65" style="background-color: white; width: 47pt; border: #ece9d8;" width="62">Jan</TD><TD class="xl65" style="background-color: white; width: 47pt; border: #ece9d8;" width="62">Jan</TD><TD class="xl65" style="background-color: white; width: 47pt; border: #ece9d8;" width="62">Feb</TD><TD class="xl65" style="background-color: white; width: 47pt; border: #ece9d8;" width="62">Feb</TD></TR><TR height="17" style="height: 12.75pt;"><TD class="xl66" height="17" style="border-bottom: black 0.5pt solid; border-left: black 0.5pt solid; background-color: #e6e6e6; height: 12.75pt; border-top: black; border-right: black 0.5pt solid;"><STRONG>Line</STRONG></TD><TD class="xl67" style="border-bottom: black 1pt solid; border-left: black 1pt solid; background-color: #e6e6e6; border-top: black; border-right: black 0.5pt solid;"><STRONG>Scenario</STRONG></TD><TD class="xl65" style="background-color: white; border: #ece9d8;">A</TD><TD class="xl65" style="background-color: white; border: #ece9d8;">F</TD><TD class="xl65" style="background-color: white; border: #ece9d8;">A</TD><TD class="xl65" style="background-color: white; border: #ece9d8;">F</TD></TR><TR height="17" style="height: 12.75pt;"><TD class="xl65" height="17" style="background-color: white; height: 12.75pt; border: #ece9d8;">Total NII</TD><TD class="xl65" style="background-color: white; border: #ece9d8;"></TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$40,437.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$43,998.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$37,057.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$39,381.</TD></TR><TR height="17" style="height: 12.75pt;"><TD class="xl65" height="17" style="background-color: white; height: 12.75pt; border: #ece9d8;">Trust Income</TD><TD class="xl65" style="background-color: white; border: #ece9d8;"></TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$10,144.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$21,554.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$6,893.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$14,893.</TD></TR><TR height="17" style="height: 12.75pt;"><TD class="xl65" height="17" style="background-color: white; height: 12.75pt; border: #ece9d8;">Service Charges</TD><TD class="xl65" style="background-color: white; border: #ece9d8;"></TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$5,284.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$5,723.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$4,560.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$4,766.</TD></TR><TR height="17" style="height: 12.75pt;"><TD class="xl65" height="17" style="background-color: white; height: 12.75pt; border: #ece9d8;">Total Mtg Banking</TD><TD class="xl65" style="background-color: white; border: #ece9d8;"></TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$19.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$20.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$11.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$11.</TD></TR><TR height="17" style="height: 12.75pt;"><TD class="xl65" height="17" style="background-color: white; height: 12.75pt; border: #ece9d8;">Brokerage Fees</TD><TD class="xl65" style="background-color: white; border: #ece9d8;"></TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$22.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$48.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$33.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$71.</TD></TR><TR height="17" style="height: 12.75pt;"><TD class="xl65" height="17" style="background-color: white; height: 12.75pt; border: #ece9d8;">Loan Fees</TD><TD class="xl65" style="background-color: white; border: #ece9d8;"></TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$6,275.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$6,646.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$4,437.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$4,771.</TD></TR><TR height="17" style="height: 12.75pt;"><TD class="xl65" height="17" style="background-color: white; height: 12.75pt; border: #ece9d8;">Merchant &amp; Credit Card</TD><TD class="xl65" style="background-color: white; border: #ece9d8;"></TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$2,164.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$2,363.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$1,895.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$1,954.</TD></TR><TR height="17" style="height: 12.75pt;"><TD class="xl65" height="17" style="background-color: white; height: 12.75pt; border: #ece9d8;">Other Fee Income</TD><TD class="xl65" style="background-color: white; border: #ece9d8;"></TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$4,231.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$8,163.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$3,948.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$6,391.</TD></TR><TR height="17" style="height: 12.75pt;"><TD class="xl69" height="17" style="background-color: transparent; height: 12.75pt; border: #ece9d8;"><STRONG>Total Other Income</STRONG></TD><TD class="xl69" style="background-color: transparent; border: #ece9d8;"><STRONG></STRONG></TD><TD align="right" class="xl70" style="background-color: white; border: #ece9d8;"><STRONG>$28,138.</STRONG></TD><TD align="right" class="xl70" style="background-color: white; border: #ece9d8;"><STRONG>$44,517.</STRONG></TD><TD align="right" class="xl70" style="background-color: white; border: #ece9d8;"><STRONG>$21,778.</STRONG></TD><TD align="right" class="xl70" style="background-color: white; border: #ece9d8;"><STRONG>$32,857.</STRONG></TD></TR><TR height="17" style="height: 12.75pt;"><TD class="xl69" height="17" style="background-color: transparent; height: 12.75pt; border: #ece9d8;"><STRONG>Total Revenue</STRONG></TD><TD class="xl69" style="background-color: transparent; border: #ece9d8;"><STRONG></STRONG></TD><TD align="right" class="xl70" style="background-color: white; border: #ece9d8;"><STRONG>$68,575.</STRONG></TD><TD align="right" class="xl70" style="background-color: white; border: #ece9d8;"><STRONG>$88,515.</STRONG></TD><TD align="right" class="xl70" style="background-color: white; border: #ece9d8;"><STRONG>$58,835.</STRONG></TD><TD align="right" class="xl70" style="background-color: white; border: #ece9d8;"><STRONG>$72,238.</STRONG></TD></TR></TBODY></TABLE><BR /><BR /></BODY></HTML> Tue, 14 Dec 2010 19:59:41 GMT 2010-12-14T19:59:41Z Pivot Table Sub Total https://community.qlik.com/t5/QlikView/Pivot-Table-Sub-Total/m-p/293720#M1200941 <HTML><HEAD></HEAD><BODY><P>I have done this already in excel but I am in the process of trying to replicate this process in QV. I think I would use the aggregation command but I am not certain on this. The bottom two lines are NOT included in the data but they need to be included in the pivot(calculated dimension maybe?)</P><P>It goes like this .. sum(trust income ....other fee income) = total other income</P><P>Sum(total NII, Total other income ) = Total Revenue</P><P>I have this data linked in access using the following field names</P><P></P><TABLE border="0" cellpadding="0" cellspacing="0" style="width: 48pt; border-collapse: collapse;" width="64"><COLGROUP><COL style="width: 48pt;" width="64" /></COLGROUP><TBODY><TR height="20" style="height: 15pt;"><TD height="20" style="background-color: transparent; width: 48pt; height: 15pt; border: #ece9d8;" width="64">Year</TD></TR><TR height="22" style="height: 16.5pt;"><TD height="22" style="background-color: transparent; height: 16.5pt; border: #ece9d8;">Scenario</TD></TR><TR height="22" style="height: 16.5pt;"><TD height="22" style="background-color: transparent; height: 16.5pt; border: #ece9d8;">Month</TD></TR><TR height="22" style="height: 16.5pt;"><TD height="22" style="background-color: transparent; height: 16.5pt; border: #ece9d8;">Business Unit</TD></TR><TR height="22" style="height: 16.5pt;"><TD height="22" style="background-color: transparent; height: 16.5pt; border: #ece9d8;">Line</TD></TR><TR height="22" style="height: 16.5pt;"><TD height="22" style="background-color: transparent; height: 16.5pt; border: #ece9d8;">amount</TD></TR></TBODY></TABLE><BR /><BR /> <P>Any ideas appreciated!</P><P></P><TABLE border="0" cellpadding="0" cellspacing="0" style="width: 328pt; border-collapse: collapse;" width="435"><COLGROUP><COL style="width: 98pt; mso-width-source: userset; mso-width-alt: 4790; mso-outline-parent: collapsed;" width="131" /><COL style="width: 42pt; mso-width-source: userset; mso-width-alt: 2048; mso-outline-parent: collapsed;" width="56" /><COL style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267; mso-outline-parent: collapsed;" width="62" /><COL style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267; mso-outline-parent: collapsed;" width="62" /><COL style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267; mso-outline-parent: collapsed;" width="62" /><COL style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267; mso-outline-parent: collapsed;" width="62" /></COLGROUP><TBODY><TR height="17" style="height: 12.75pt;"><TD class="xl63" height="17" style="border-bottom: black 1pt solid; border-left: black 0.5pt solid; background-color: silver; width: 98pt; height: 12.75pt; border-top: black 0.5pt solid; border-right: black 1pt solid;" width="131"><STRONG></STRONG></TD><TD class="xl64" style="border-bottom: black 0.5pt solid; border-left: black; background-color: #e6e6e6; width: 42pt; border-top: black 0.5pt solid; border-right: black 0.5pt solid;" width="56"><STRONG>Month</STRONG></TD><TD class="xl65" style="background-color: white; width: 47pt; border: #ece9d8;" width="62">Jan</TD><TD class="xl65" style="background-color: white; width: 47pt; border: #ece9d8;" width="62">Jan</TD><TD class="xl65" style="background-color: white; width: 47pt; border: #ece9d8;" width="62">Feb</TD><TD class="xl65" style="background-color: white; width: 47pt; border: #ece9d8;" width="62">Feb</TD></TR><TR height="17" style="height: 12.75pt;"><TD class="xl66" height="17" style="border-bottom: black 0.5pt solid; border-left: black 0.5pt solid; background-color: #e6e6e6; height: 12.75pt; border-top: black; border-right: black 0.5pt solid;"><STRONG>Line</STRONG></TD><TD class="xl67" style="border-bottom: black 1pt solid; border-left: black 1pt solid; background-color: #e6e6e6; border-top: black; border-right: black 0.5pt solid;"><STRONG>Scenario</STRONG></TD><TD class="xl65" style="background-color: white; border: #ece9d8;">A</TD><TD class="xl65" style="background-color: white; border: #ece9d8;">F</TD><TD class="xl65" style="background-color: white; border: #ece9d8;">A</TD><TD class="xl65" style="background-color: white; border: #ece9d8;">F</TD></TR><TR height="17" style="height: 12.75pt;"><TD class="xl65" height="17" style="background-color: white; height: 12.75pt; border: #ece9d8;">Total NII</TD><TD class="xl65" style="background-color: white; border: #ece9d8;"></TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$40,437.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$43,998.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$37,057.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$39,381.</TD></TR><TR height="17" style="height: 12.75pt;"><TD class="xl65" height="17" style="background-color: white; height: 12.75pt; border: #ece9d8;">Trust Income</TD><TD class="xl65" style="background-color: white; border: #ece9d8;"></TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$10,144.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$21,554.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$6,893.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$14,893.</TD></TR><TR height="17" style="height: 12.75pt;"><TD class="xl65" height="17" style="background-color: white; height: 12.75pt; border: #ece9d8;">Service Charges</TD><TD class="xl65" style="background-color: white; border: #ece9d8;"></TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$5,284.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$5,723.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$4,560.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$4,766.</TD></TR><TR height="17" style="height: 12.75pt;"><TD class="xl65" height="17" style="background-color: white; height: 12.75pt; border: #ece9d8;">Total Mtg Banking</TD><TD class="xl65" style="background-color: white; border: #ece9d8;"></TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$19.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$20.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$11.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$11.</TD></TR><TR height="17" style="height: 12.75pt;"><TD class="xl65" height="17" style="background-color: white; height: 12.75pt; border: #ece9d8;">Brokerage Fees</TD><TD class="xl65" style="background-color: white; border: #ece9d8;"></TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$22.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$48.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$33.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$71.</TD></TR><TR height="17" style="height: 12.75pt;"><TD class="xl65" height="17" style="background-color: white; height: 12.75pt; border: #ece9d8;">Loan Fees</TD><TD class="xl65" style="background-color: white; border: #ece9d8;"></TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$6,275.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$6,646.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$4,437.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$4,771.</TD></TR><TR height="17" style="height: 12.75pt;"><TD class="xl65" height="17" style="background-color: white; height: 12.75pt; border: #ece9d8;">Merchant &amp; Credit Card</TD><TD class="xl65" style="background-color: white; border: #ece9d8;"></TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$2,164.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$2,363.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$1,895.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$1,954.</TD></TR><TR height="17" style="height: 12.75pt;"><TD class="xl65" height="17" style="background-color: white; height: 12.75pt; border: #ece9d8;">Other Fee Income</TD><TD class="xl65" style="background-color: white; border: #ece9d8;"></TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$4,231.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$8,163.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$3,948.</TD><TD align="right" class="xl68" style="background-color: white; border: #ece9d8;">$6,391.</TD></TR><TR height="17" style="height: 12.75pt;"><TD class="xl69" height="17" style="background-color: transparent; height: 12.75pt; border: #ece9d8;"><STRONG>Total Other Income</STRONG></TD><TD class="xl69" style="background-color: transparent; border: #ece9d8;"><STRONG></STRONG></TD><TD align="right" class="xl70" style="background-color: white; border: #ece9d8;"><STRONG>$28,138.</STRONG></TD><TD align="right" class="xl70" style="background-color: white; border: #ece9d8;"><STRONG>$44,517.</STRONG></TD><TD align="right" class="xl70" style="background-color: white; border: #ece9d8;"><STRONG>$21,778.</STRONG></TD><TD align="right" class="xl70" style="background-color: white; border: #ece9d8;"><STRONG>$32,857.</STRONG></TD></TR><TR height="17" style="height: 12.75pt;"><TD class="xl69" height="17" style="background-color: transparent; height: 12.75pt; border: #ece9d8;"><STRONG>Total Revenue</STRONG></TD><TD class="xl69" style="background-color: transparent; border: #ece9d8;"><STRONG></STRONG></TD><TD align="right" class="xl70" style="background-color: white; border: #ece9d8;"><STRONG>$68,575.</STRONG></TD><TD align="right" class="xl70" style="background-color: white; border: #ece9d8;"><STRONG>$88,515.</STRONG></TD><TD align="right" class="xl70" style="background-color: white; border: #ece9d8;"><STRONG>$58,835.</STRONG></TD><TD align="right" class="xl70" style="background-color: white; border: #ece9d8;"><STRONG>$72,238.</STRONG></TD></TR></TBODY></TABLE><BR /><BR /></BODY></HTML> Tue, 14 Dec 2010 19:59:41 GMT https://community.qlik.com/t5/QlikView/Pivot-Table-Sub-Total/m-p/293720#M1200941 2010-12-14T19:59:41Z