topic Re: aggregating distinct sum? in QlikView https://community.qlik.com/t5/QlikView/aggregating-distinct-sum/m-p/883949#M308257 <HTML><HEAD></HEAD><BODY><P>Try sum( <STRONG>distinct</STRONG> if(....) )</P></BODY></HTML> Tue, 23 Jun 2015 15:02:43 GMT Gysbert_Wassenaar 2015-06-23T15:02:43Z aggregating distinct sum? https://community.qlik.com/t5/QlikView/aggregating-distinct-sum/m-p/883948#M308256 <HTML><HEAD></HEAD><BODY><P>Hello,</P><P><BR />Can somebodey please help me?</P><P></P><P>I have a date island and would like to do a sum on some table rows but grouped by a field</P><P></P><P>for example table, with dates...</P><P>TABLE1</P><P>name&nbsp;&nbsp;&nbsp;&nbsp; amount1&nbsp;&nbsp;&nbsp;&nbsp; amount2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dateNum</P><P>A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 01/01/2015&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ...</P><P>A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 01/01/2015</P><P>A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 30&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ...</P><P>B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 11&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20</P><P>B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 11&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20</P><P>B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 22&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20</P><P>C&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 02/01/2015</P><P>D&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 01/01/2015</P><P>...</P><P></P><P>In my chart i have "DateIsland.YearMonth" as dimension,</P><P></P><P>Next in my expressions for example when dimension reaches "01/2015"</P><P></P><P>For amount1 i do "=SUM(if(TABLE1.dateNum = DateIsland.dateNum, amount1))" because amount1 may be summarized as normal, so i get 114</P><P>For amount2 i would like to do a <SPAN style="font-size: 13.3333330154419px;">"=SUM(if(TABLE1.dateNum = DateIsland.dateNum, amount2))" but group this by name so it would only pick A=10, B=20, D=10 and show in chart only 40 and not 100</SPAN></P><P></P><P>Thanks in advance for the help!</P></BODY></HTML> Tue, 23 Jun 2015 14:48:45 GMT https://community.qlik.com/t5/QlikView/aggregating-distinct-sum/m-p/883948#M308256 2015-06-23T14:48:45Z Re: aggregating distinct sum? https://community.qlik.com/t5/QlikView/aggregating-distinct-sum/m-p/883949#M308257 <HTML><HEAD></HEAD><BODY><P>Try sum( <STRONG>distinct</STRONG> if(....) )</P></BODY></HTML> Tue, 23 Jun 2015 15:02:43 GMT https://community.qlik.com/t5/QlikView/aggregating-distinct-sum/m-p/883949#M308257 Gysbert_Wassenaar 2015-06-23T15:02:43Z Re: aggregating distinct sum? https://community.qlik.com/t5/QlikView/aggregating-distinct-sum/m-p/883950#M308258 <HTML><HEAD></HEAD><BODY><P>Hello, thanks but this is not possible because i have for also another name let's say D that has also amount 10, i have edited my start post</P></BODY></HTML> Tue, 23 Jun 2015 15:13:48 GMT https://community.qlik.com/t5/QlikView/aggregating-distinct-sum/m-p/883950#M308258 2015-06-23T15:13:48Z Re: aggregating distinct sum? https://community.qlik.com/t5/QlikView/aggregating-distinct-sum/m-p/883951#M308259 <HTML><HEAD></HEAD><BODY><P>Perhaps sum(aggr(sum(distinct if(...) ), name, <SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 12px;">DateIsland.dateNum))</SPAN></P></BODY></HTML> Tue, 23 Jun 2015 15:19:01 GMT https://community.qlik.com/t5/QlikView/aggregating-distinct-sum/m-p/883951#M308259 Gysbert_Wassenaar 2015-06-23T15:19:01Z Re: aggregating distinct sum? https://community.qlik.com/t5/QlikView/aggregating-distinct-sum/m-p/883952#M308260 <HTML><HEAD></HEAD><BODY><P>That seems to work for now, thank you very much!</P></BODY></HTML> Wed, 24 Jun 2015 11:58:04 GMT https://community.qlik.com/t5/QlikView/aggregating-distinct-sum/m-p/883952#M308260 2015-06-24T11:58:04Z