topic Re: To avoid calculation on repeated reference numbers in QlikView https://community.qlik.com/t5/QlikView/To-avoid-calculation-on-repeated-reference-numbers/m-p/1391661#M423069 <HTML><HEAD></HEAD><BODY><P>NOT YET REPLIED. PLEASE RESPONSE</P></BODY></HTML> Sat, 12 Aug 2017 14:14:18 GMT nevilledhamsiri 2017-08-12T14:14:18Z To avoid calculation on repeated reference numbers https://community.qlik.com/t5/QlikView/To-avoid-calculation-on-repeated-reference-numbers/m-p/1391660#M423068 <HTML><HEAD></HEAD><BODY><P>I need to calculate penalty of Rs250 for each cancelled policies. I base their reference numbers to do this.But some reference numbers are repeated &amp; I should do the above calculation excluding those repeated numbers.In other words, Rs 250 to be charged only against one reference number. Here the reference numbers are dimension &amp; the related premium value due to these reference numbers too are assigned which is a expression. To make this clear, I may present my case as below:</P><P>Ref_Number&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Class&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Policy_Number&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Premium(Rs)</P><P>RA000C125&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MC&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RA1C&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 500</P><P>RA000C125&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MC&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RA1C&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 250</P><P>RA000C128&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FR&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RA1D&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 320</P><P>RA000C150&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FR&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RA1K&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 250&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </P><P>EM000C200&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MC&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EM1A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100</P><P>EM000C300&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FR&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EM1B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 200</P><P>EM000C200&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MC&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EM1A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 50</P><P></P><P>I have written expression as follows!</P><P>sum({&lt;Ref_Number={'MC'}&gt;}250)</P><P>As per above, Rs 250 is calculated<SPAN style="color: #575757; text-decoration: underline;"> <STRONG>twice</STRONG></SPAN> on RA000125, EM000C200 Ref numbers. Help me to avoid this by having included some logic in to my above set analysis.</P><P>Thanks.</P><P>Neville</P></BODY></HTML> Sat, 12 Aug 2017 02:38:51 GMT https://community.qlik.com/t5/QlikView/To-avoid-calculation-on-repeated-reference-numbers/m-p/1391660#M423068 nevilledhamsiri 2017-08-12T02:38:51Z Re: To avoid calculation on repeated reference numbers https://community.qlik.com/t5/QlikView/To-avoid-calculation-on-repeated-reference-numbers/m-p/1391661#M423069 <HTML><HEAD></HEAD><BODY><P>NOT YET REPLIED. PLEASE RESPONSE</P></BODY></HTML> Sat, 12 Aug 2017 14:14:18 GMT https://community.qlik.com/t5/QlikView/To-avoid-calculation-on-repeated-reference-numbers/m-p/1391661#M423069 nevilledhamsiri 2017-08-12T14:14:18Z Re: To avoid calculation on repeated reference numbers https://community.qlik.com/t5/QlikView/To-avoid-calculation-on-repeated-reference-numbers/m-p/1391662#M423070 <HTML><HEAD></HEAD><BODY><P>250 is calculated twice? I am not sure I understand?</P></BODY></HTML> Sat, 12 Aug 2017 16:55:51 GMT https://community.qlik.com/t5/QlikView/To-avoid-calculation-on-repeated-reference-numbers/m-p/1391662#M423070 sunny_talwar 2017-08-12T16:55:51Z Re: To avoid calculation on repeated reference numbers https://community.qlik.com/t5/QlikView/To-avoid-calculation-on-repeated-reference-numbers/m-p/1391663#M423071 <HTML><HEAD></HEAD><BODY><P>I think, you need something like below</P><P></P><P><STRONG>FirstSortedValue(DISTINCT [</STRONG><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;"><STRONG>Premium(Rs)], -Aggr(Sum([</STRONG><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;"><STRONG>Premium(Rs)], Ref_Number)</STRONG></SPAN></SPAN></P><P><SPAN style="color: #3d3d3d; font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif;"><STRONG><BR /></STRONG></SPAN></P><P><SPAN style="color: #3d3d3d; font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif;"><STRONG>OR</STRONG></SPAN></P><P><SPAN style="color: #3d3d3d; font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif;"><STRONG><BR /></STRONG></SPAN></P><P><SPAN style="color: #3d3d3d; font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif;"><STRONG><STRONG style="font-size: 13.3333px;">Aggr(FirstSortedValue(DISTINCT [</STRONG><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;"><STRONG>Premium(Rs)], -Aggr(Sum([</STRONG><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;"><STRONG>Premium(Rs)], Ref_Number), NonAggrDim, ...)</STRONG></SPAN></SPAN></STRONG></SPAN></P></BODY></HTML> Tue, 15 Aug 2017 00:03:51 GMT https://community.qlik.com/t5/QlikView/To-avoid-calculation-on-repeated-reference-numbers/m-p/1391663#M423071 Anil_Babu_Samineni 2017-08-15T00:03:51Z