topic Re: Get Total Value Regardless Selection in QlikView https://community.qlik.com/t5/QlikView/Get-Total-Value-Regardless-Selection/m-p/393608#M484526 <HTML><HEAD></HEAD><BODY><P>Hi Adrian,</P><P></P><P>K.I.S.S </P><P>=Avg({$&lt;City = &gt;} Score)</P><P></P><P>Regards,</P><P>Sokkorn</P></BODY></HTML> Wed, 22 May 2013 03:51:08 GMT Sokkorn 2013-05-22T03:51:08Z Get Total Value Regardless Selection https://community.qlik.com/t5/QlikView/Get-Total-Value-Regardless-Selection/m-p/393607#M484525 <HTML><HEAD></HEAD><BODY><P>Hi QV Expert!</P><P></P><P>I have this data:</P><P></P><TABLE border="1" cellpadding="3" cellspacing="0" class="jiveBorder" height="154" style="background-color: #ffffff; border: 1px solid #000000; margin: 1px 0px; padding: 3px; color: #737373; font-family: Arial; width: 427px; height: 162px;"><TBODY><TR style="border: 0px;"><TH align="center" style="border: 1px solid #000000; background-color: #6690bc;" valign="middle"><SPAN style="color: #ffffff;"><STRONG>Period</STRONG></SPAN></TH><TH align="center" style="border: 1px solid #000000; background-color: #6690bc;" valign="middle"><SPAN style="color: #ffffff;"><STRONG> City</STRONG></SPAN></TH><TH align="center" style="border: 1px solid #000000; background-color: #6690bc;" valign="middle"><SPAN style="color: #ffffff;"><STRONG>Score</STRONG></SPAN></TH></TR><TR style="border: 0px;"><TD style="border: 1px solid #000000; margin: 1px; padding: 3px;">2010</TD><TD style="border: 1px solid #000000; margin: 1px; padding: 3px;"><STRONG>Jakarta</STRONG></TD><TD style="border: 1px solid #000000; margin: 1px; padding: 3px;">12</TD></TR><TR style="border: 0px;"><TD style="border: 1px solid #000000; margin: 1px; padding: 3px;">2010</TD><TD style="border: 1px solid #000000; margin: 1px; padding: 3px;"><SPAN style="color: #737373; background-color: #ffffff; font-family: Arial;">New York</SPAN></TD><TD style="border: 1px solid #000000; margin: 1px; padding: 3px;">30</TD></TR><TR style="border: 0px;"><TD style="border: 1px solid #000000; margin: 1px; padding: 3px;"><SPAN style="color: #737373; font-family: Arial; background-color: #ffffff;">2010</SPAN></TD><TD style="border: 1px solid #000000; margin: 1px; padding: 3px;"><SPAN style="color: #737373; background-color: #ffffff; font-family: Arial;">Melbourne</SPAN></TD><TD style="border: 1px solid #000000; margin: 1px; padding: 3px;">5</TD></TR><TR style="border: 0px;"><TD style="border: 1px solid #000000; margin: 1px; padding: 3px;"><SPAN style="color: #737373; font-family: Arial; background-color: #ffffff;">2010</SPAN></TD><TD style="border: 1px solid #000000; margin: 1px; padding: 3px;"><STRONG>Madrid</STRONG></TD><TD style="border: 1px solid #000000; margin: 1px; padding: 3px;">58</TD></TR><TR style="border: 0px;"><TD style="border: 1px solid #000000; margin: 1px; padding: 3px;"><SPAN style="color: #737373; font-family: Arial; background-color: #ffffff;">2010</SPAN></TD><TD style="border: 1px solid #000000; margin: 1px; padding: 3px;">Tokyo</TD><TD style="border: 1px solid #000000; margin: 1px; padding: 3px;">15</TD></TR><TR style="border: 0px;"><TD style="border: 1px solid #000000; margin: 1px; padding: 3px;"><SPAN style="color: #737373; font-family: Arial; background-color: #ffffff;">2010</SPAN></TD><TD style="border: 1px solid #000000; margin: 1px; padding: 3px;">Prague</TD><TD style="border: 1px solid #000000; margin: 1px; padding: 3px;">6</TD></TR><TR><TD>2011</TD><TD><SPAN style="color: #737373; font-family: Arial; background-color: #ffffff;">Jakarta</SPAN></TD><TD>22</TD></TR><TR><TD>2011</TD><TD><SPAN style="color: #737373; font-family: Arial; background-color: #ffffff;">New York</SPAN></TD><TD>29</TD></TR><TR><TD>2011</TD><TD><SPAN style="color: #737373; font-family: Arial; background-color: #ffffff;">Melbourne</SPAN></TD><TD>4</TD></TR><TR><TD><SPAN style="color: #3366ff;"><EM>... and so on</EM></SPAN></TD><TD><SPAN style="color: #3366ff; background-color: #ffffff; font-family: Arial;"><EM>... and so on</EM></SPAN></TD><TD><SPAN style="color: #3366ff; background-color: #ffffff; font-family: Arial;"><EM>... and so on</EM></SPAN></TD></TR></TBODY></TABLE><P></P><P>I have a trouble here, please help me how to accomplish this:</P><UL><LI><STRONG>X</STRONG> = Get AVERAGE VALUE of SCORE in a PERIOD from ALL CITIES regardless how many cities selected.</LI></UL><P></P><P>Thus, <STRONG>X</STRONG> will always <STRONG>21 </STRONG>in selected period = <STRONG>2010</STRONG> even if selected city =<STRONG> Jakarta, Madrid</STRONG></P><P></P><P>I have this formula, but it doesn't work.</P><P></P><P><SPAN style="font-family: 'courier new', courier;">=Avg({&lt;Period={$(=GetFieldSelections(<SPAN style="font-family: 'courier new', courier;">Period</SPAN>))}, City={$(=Concat{DISTINCT {1} City, ','})}&gt;} Score)</SPAN></P><P></P><P></P><P>Please help.</P><P>Thank you.</P><P></P><P></P><P>Attached 2 files I used to create the report:</P><OL><LI><SPAN style="font-family: 'courier new', courier;">sample_online.xlsx</SPAN> (take a look at the <SPAN style="font-family: 'courier new', courier;">hardcode </SPAN>sheet)</LI><LI><SPAN style="font-family: 'courier new', courier;">test_sample_online.qvw</SPAN> (go to <SPAN style="font-family: 'courier new', courier;">Report </SPAN>sheet)</LI></OL></BODY></HTML> Wed, 22 May 2013 03:38:52 GMT https://community.qlik.com/t5/QlikView/Get-Total-Value-Regardless-Selection/m-p/393607#M484525 2013-05-22T03:38:52Z Re: Get Total Value Regardless Selection https://community.qlik.com/t5/QlikView/Get-Total-Value-Regardless-Selection/m-p/393608#M484526 <HTML><HEAD></HEAD><BODY><P>Hi Adrian,</P><P></P><P>K.I.S.S </P><P>=Avg({$&lt;City = &gt;} Score)</P><P></P><P>Regards,</P><P>Sokkorn</P></BODY></HTML> Wed, 22 May 2013 03:51:08 GMT https://community.qlik.com/t5/QlikView/Get-Total-Value-Regardless-Selection/m-p/393608#M484526 Sokkorn 2013-05-22T03:51:08Z