topic Re: Sum, aggr and total in QlikView https://community.qlik.com/t5/QlikView/Sum-aggr-and-total/m-p/1159412#M629986 <HTML><HEAD></HEAD><BODY><P><SPAN style="font-size: 13.3333px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;"><STRONG>AGGR(Sum({$&lt;<SPAN style="font-weight: inherit; font-style: inherit; font-size: 13px;">type2 = {'1*'}&gt;</SPAN>}TOTAL transactions),Type)</STRONG></SPAN></P></BODY></HTML> Wed, 07 Sep 2016 20:19:10 GMT NickHoff 2016-09-07T20:19:10Z Sum, aggr and total https://community.qlik.com/t5/QlikView/Sum-aggr-and-total/m-p/1159405#M629979 <HTML><HEAD></HEAD><BODY><P>Hi everyone!</P><P></P><P>I have the next data:</P><P></P><P>type&nbsp; transactions&nbsp; client&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; type2</P><P>1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; name1&nbsp;&nbsp;&nbsp;&nbsp; 123</P><P>2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; name2&nbsp;&nbsp;&nbsp;&nbsp; 234</P><P>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 11&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; name3&nbsp;&nbsp;&nbsp;&nbsp; 345</P><P>1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; name4&nbsp;&nbsp;&nbsp; 133</P><P>......</P><P></P><P></P><P>There are a lot of types.</P><P>I create a pivot table with the dimension type and, for each one, i created a calculated dimension:</P><P></P><P>=IF(</P><P>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; aggr(</P><P>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rank(</P><P>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUM(</P><P>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {&lt;</P><P>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; type2 = {'1*'}</P><P>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;}</P><P>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; transactions</P><P>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )</P><P>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ),type, client) &lt;= 10,</P><P>&nbsp; client)</P><P></P><P>this bring to me the top ten client with the maximun transactions (in the expression I sum the transactions where type2 = {'1*'})</P><P>This is ok, but, i need another expression that sum all the transactions grouped by type (for the example data for the type 1 the sum is 7), i try with aggr, sum and total but it doesnt work.</P><P></P><P></P><P>Thanks a lot!!</P><P></P><P>Upload example!</P></BODY></HTML> Wed, 07 Sep 2016 12:23:33 GMT https://community.qlik.com/t5/QlikView/Sum-aggr-and-total/m-p/1159405#M629979 juan_patrick 2016-09-07T12:23:33Z Re: Sum, aggr and total https://community.qlik.com/t5/QlikView/Sum-aggr-and-total/m-p/1159406#M629980 <HTML><HEAD></HEAD><BODY><P>What is the expected O/P from given table</P></BODY></HTML> Wed, 07 Sep 2016 15:24:59 GMT https://community.qlik.com/t5/QlikView/Sum-aggr-and-total/m-p/1159406#M629980 Anil_Babu_Samineni 2016-09-07T15:24:59Z Re: Sum, aggr and total https://community.qlik.com/t5/QlikView/Sum-aggr-and-total/m-p/1159407#M629981 <HTML><HEAD></HEAD><BODY><P>TYPE&nbsp;&nbsp; TOTAL TRX</P><P>1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7</P><P>2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0</P><P>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0</P></BODY></HTML> Wed, 07 Sep 2016 15:26:58 GMT https://community.qlik.com/t5/QlikView/Sum-aggr-and-total/m-p/1159407#M629981 juan_patrick 2016-09-07T15:26:58Z Re: Sum, aggr and total https://community.qlik.com/t5/QlikView/Sum-aggr-and-total/m-p/1159408#M629982 <HTML><HEAD></HEAD><BODY><P>Hi Juan,</P><P>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Have you tried</P><P></P><P><SPAN style="text-decoration: line-through;">Sum(TOTAL &lt;type&gt; transactions)</SPAN> ?</P><P></P><P></P><P><STRONG>EDIT :</STRONG><SPAN style="font-size: 13.3333px;"><STRONG>Sum({$&lt;<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">type2 = {'1*'}&gt;</SPAN>}TOTAL &lt;type&gt; transactions)</STRONG></SPAN></P><P></P><P>Kind regards</P><P></P><P>Andrew</P></BODY></HTML> Wed, 07 Sep 2016 15:30:17 GMT https://community.qlik.com/t5/QlikView/Sum-aggr-and-total/m-p/1159408#M629982 effinty2112 2016-09-07T15:30:17Z Re: Sum, aggr and total https://community.qlik.com/t5/QlikView/Sum-aggr-and-total/m-p/1159409#M629983 <HTML><HEAD></HEAD><BODY><P>It give me back the total for each client, not for each type</P></BODY></HTML> Wed, 07 Sep 2016 15:41:00 GMT https://community.qlik.com/t5/QlikView/Sum-aggr-and-total/m-p/1159409#M629983 juan_patrick 2016-09-07T15:41:00Z Re: Sum, aggr and total https://community.qlik.com/t5/QlikView/Sum-aggr-and-total/m-p/1159410#M629984 <HTML><HEAD></HEAD><BODY><P>It doesnt work</P></BODY></HTML> Wed, 07 Sep 2016 16:09:59 GMT https://community.qlik.com/t5/QlikView/Sum-aggr-and-total/m-p/1159410#M629984 juan_patrick 2016-09-07T16:09:59Z Re: Sum, aggr and total https://community.qlik.com/t5/QlikView/Sum-aggr-and-total/m-p/1159411#M629985 <HTML><HEAD></HEAD><BODY><P>I would have assumed <A href="https://community.qlik.com/people/effinty2112">effinty2112</A>‌'s expression to have worked, would you be able to provide a sample to show where it isn't working for you?</P></BODY></HTML> Wed, 07 Sep 2016 19:18:55 GMT https://community.qlik.com/t5/QlikView/Sum-aggr-and-total/m-p/1159411#M629985 sunny_talwar 2016-09-07T19:18:55Z Re: Sum, aggr and total https://community.qlik.com/t5/QlikView/Sum-aggr-and-total/m-p/1159412#M629986 <HTML><HEAD></HEAD><BODY><P><SPAN style="font-size: 13.3333px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;"><STRONG>AGGR(Sum({$&lt;<SPAN style="font-weight: inherit; font-style: inherit; font-size: 13px;">type2 = {'1*'}&gt;</SPAN>}TOTAL transactions),Type)</STRONG></SPAN></P></BODY></HTML> Wed, 07 Sep 2016 20:19:10 GMT https://community.qlik.com/t5/QlikView/Sum-aggr-and-total/m-p/1159412#M629986 NickHoff 2016-09-07T20:19:10Z Re: Sum, aggr and total https://community.qlik.com/t5/QlikView/Sum-aggr-and-total/m-p/1159413#M629987 <HTML><HEAD></HEAD><BODY><P>it dosnt work, i need it in every row</P></BODY></HTML> Wed, 07 Sep 2016 20:21:43 GMT https://community.qlik.com/t5/QlikView/Sum-aggr-and-total/m-p/1159413#M629987 juan_patrick 2016-09-07T20:21:43Z Re: Sum, aggr and total https://community.qlik.com/t5/QlikView/Sum-aggr-and-total/m-p/1159414#M629988 <HTML><HEAD></HEAD><BODY><P>May be this:</P><P></P><P><STRONG>Aggr(NODISTINCT Sum({&lt;type2 = {'1*'}&gt;} transactions), type)</STRONG></P><P><STRONG><BR /></STRONG></P><P><STRONG><IMG alt="Capture.PNG" class="jive-image image-1" src="https://community.qlik.com/legacyfs/online/136739_Capture.PNG" style="height: auto;" /><BR /></STRONG></P></BODY></HTML> Wed, 07 Sep 2016 20:32:45 GMT https://community.qlik.com/t5/QlikView/Sum-aggr-and-total/m-p/1159414#M629988 sunny_talwar 2016-09-07T20:32:45Z Re: Sum, aggr and total https://community.qlik.com/t5/QlikView/Sum-aggr-and-total/m-p/1159415#M629989 <HTML><HEAD></HEAD><BODY><P>Great!</P><P></P><P>Thank you very much!</P></BODY></HTML> Thu, 08 Sep 2016 16:19:08 GMT https://community.qlik.com/t5/QlikView/Sum-aggr-and-total/m-p/1159415#M629989 juan_patrick 2016-09-08T16:19:08Z