topic Re: How to get TOP 3 and Bottom 3 AVG and Max Difference value with rank function and AGGR. in QlikView https://community.qlik.com/t5/QlikView/How-to-get-TOP-3-and-Bottom-3-AVG-and-Max-Difference-value-with/m-p/32756#M791012 <HTML><HEAD></HEAD><BODY><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">Use this for&nbsp; <STRONG style="font-style: inherit; font-family: inherit;">Top:</STRONG></P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">=IF (aggr(rank( sum(Value) ,0) , Value )&lt;= 3 , (sum(Value)/3) )</P><P></P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;"><STRONG>Bottom</STRONG>column:</P><P></P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">=IF (aggr(rank( sum(Value) ,0) , Value )&gt; 3 , (sum(Value)/3) )</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;"></P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">in 3 column write like(i mean subtract column 2 - column 1) 'measure 2'-'measure 1 '</P></BODY></HTML> Thu, 01 Feb 2018 06:51:04 GMT ramasaisaksoft 2018-02-01T06:51:04Z How to get TOP 3 and Bottom 3 AVG and Max Difference value with rank function and AGGR. https://community.qlik.com/t5/QlikView/How-to-get-TOP-3-and-Bottom-3-AVG-and-Max-Difference-value-with/m-p/32755#M791011 <HTML><HEAD></HEAD><BODY><P>We are having the requirement of top 3 and Bottom 3 avg of value.</P><P>Max of difference of Avg of top 3 and bottom 3.</P></BODY></HTML> Wed, 25 Nov 2020 16:16:04 GMT https://community.qlik.com/t5/QlikView/How-to-get-TOP-3-and-Bottom-3-AVG-and-Max-Difference-value-with/m-p/32755#M791011 Anonymous 2020-11-25T16:16:04Z Re: How to get TOP 3 and Bottom 3 AVG and Max Difference value with rank function and AGGR. https://community.qlik.com/t5/QlikView/How-to-get-TOP-3-and-Bottom-3-AVG-and-Max-Difference-value-with/m-p/32756#M791012 <HTML><HEAD></HEAD><BODY><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">Use this for&nbsp; <STRONG style="font-style: inherit; font-family: inherit;">Top:</STRONG></P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">=IF (aggr(rank( sum(Value) ,0) , Value )&lt;= 3 , (sum(Value)/3) )</P><P></P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;"><STRONG>Bottom</STRONG>column:</P><P></P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">=IF (aggr(rank( sum(Value) ,0) , Value )&gt; 3 , (sum(Value)/3) )</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;"></P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">in 3 column write like(i mean subtract column 2 - column 1) 'measure 2'-'measure 1 '</P></BODY></HTML> Thu, 01 Feb 2018 06:51:04 GMT https://community.qlik.com/t5/QlikView/How-to-get-TOP-3-and-Bottom-3-AVG-and-Max-Difference-value-with/m-p/32756#M791012 ramasaisaksoft 2018-02-01T06:51:04Z Re: How to get TOP 3 and Bottom 3 AVG and Max Difference value with rank function and AGGR. https://community.qlik.com/t5/QlikView/How-to-get-TOP-3-and-Bottom-3-AVG-and-Max-Difference-value-with/m-p/32757#M791013 <HTML><HEAD></HEAD><BODY><P>Hi Dilip,</P><P>here is the formula for </P><P>For top 3</P><P>If(Aggr(Rank(Avg(Value))&lt;=3,"Your Dimension"),"<SPAN style="font-size: 13.3333px;">Your Dimension")</SPAN></P><P>For bottom 3 </P><P>=If(Aggr(Rank(-Avg(Value)) &lt;= 3,"<SPAN style="font-size: 13.3333px;">Your Dimension"),</SPAN>"<SPAN style="font-size: 13.3333px;">Your Dimension")</SPAN></P><P>Many Thanks</P><P>Karthik</P><P></P></BODY></HTML> Thu, 01 Feb 2018 06:53:28 GMT https://community.qlik.com/t5/QlikView/How-to-get-TOP-3-and-Bottom-3-AVG-and-Max-Difference-value-with/m-p/32757#M791013 karthikoffi27se 2018-02-01T06:53:28Z Re: How to get TOP 3 and Bottom 3 AVG and Max Difference value with rank function and AGGR. https://community.qlik.com/t5/QlikView/How-to-get-TOP-3-and-Bottom-3-AVG-and-Max-Difference-value-with/m-p/32758#M791014 <HTML><HEAD></HEAD><BODY><P>tried Top 3.. we are not getting the value.</P></BODY></HTML> Thu, 01 Feb 2018 07:11:04 GMT https://community.qlik.com/t5/QlikView/How-to-get-TOP-3-and-Bottom-3-AVG-and-Max-Difference-value-with/m-p/32758#M791014 Anonymous 2018-02-01T07:11:04Z Re: How to get TOP 3 and Bottom 3 AVG and Max Difference value with rank function and AGGR. https://community.qlik.com/t5/QlikView/How-to-get-TOP-3-and-Bottom-3-AVG-and-Max-Difference-value-with/m-p/32759#M791015 <HTML><HEAD></HEAD><BODY><P>tried above expression. we are not getting the values.</P></BODY></HTML> Thu, 01 Feb 2018 07:13:40 GMT https://community.qlik.com/t5/QlikView/How-to-get-TOP-3-and-Bottom-3-AVG-and-Max-Difference-value-with/m-p/32759#M791015 Anonymous 2018-02-01T07:13:40Z Re: How to get TOP 3 and Bottom 3 AVG and Max Difference value with rank function and AGGR. https://community.qlik.com/t5/QlikView/How-to-get-TOP-3-and-Bottom-3-AVG-and-Max-Difference-value-with/m-p/32760#M791016 <HTML><HEAD></HEAD><BODY><P>hi </P><P>have a look at the attach example </P></BODY></HTML> Thu, 01 Feb 2018 08:44:25 GMT https://community.qlik.com/t5/QlikView/How-to-get-TOP-3-and-Bottom-3-AVG-and-Max-Difference-value-with/m-p/32760#M791016 lironbaram 2018-02-01T08:44:25Z Re: How to get TOP 3 and Bottom 3 AVG and Max Difference value with rank function and AGGR. https://community.qlik.com/t5/QlikView/How-to-get-TOP-3-and-Bottom-3-AVG-and-Max-Difference-value-with/m-p/32761#M791017 <HTML><HEAD></HEAD><BODY><P>how to get in table..</P></BODY></HTML> Thu, 01 Feb 2018 09:35:52 GMT https://community.qlik.com/t5/QlikView/How-to-get-TOP-3-and-Bottom-3-AVG-and-Max-Difference-value-with/m-p/32761#M791017 Anonymous 2018-02-01T09:35:52Z Re: How to get TOP 3 and Bottom 3 AVG and Max Difference value with rank function and AGGR. https://community.qlik.com/t5/QlikView/How-to-get-TOP-3-and-Bottom-3-AVG-and-Max-Difference-value-with/m-p/32762#M791018 <HTML><HEAD></HEAD><BODY><P>what is the dimension of the table ?</P></BODY></HTML> Thu, 01 Feb 2018 09:42:40 GMT https://community.qlik.com/t5/QlikView/How-to-get-TOP-3-and-Bottom-3-AVG-and-Max-Difference-value-with/m-p/32762#M791018 lironbaram 2018-02-01T09:42:40Z Re: How to get TOP 3 and Bottom 3 AVG and Max Difference value with rank function and AGGR. https://community.qlik.com/t5/QlikView/How-to-get-TOP-3-and-Bottom-3-AVG-and-Max-Difference-value-with/m-p/32763#M791019 <HTML><HEAD></HEAD><BODY><P>Dimension : P_id</P><P>Value :tpt (value)</P></BODY></HTML> Thu, 01 Feb 2018 09:46:04 GMT https://community.qlik.com/t5/QlikView/How-to-get-TOP-3-and-Bottom-3-AVG-and-Max-Difference-value-with/m-p/32763#M791019 Anonymous 2018-02-01T09:46:04Z Re: How to get TOP 3 and Bottom 3 AVG and Max Difference value with rank function and AGGR. https://community.qlik.com/t5/QlikView/How-to-get-TOP-3-and-Bottom-3-AVG-and-Max-Difference-value-with/m-p/32764#M791020 <HTML><HEAD></HEAD><BODY><P>can you upload a model with sample data</P></BODY></HTML> Thu, 01 Feb 2018 10:09:00 GMT https://community.qlik.com/t5/QlikView/How-to-get-TOP-3-and-Bottom-3-AVG-and-Max-Difference-value-with/m-p/32764#M791020 lironbaram 2018-02-01T10:09:00Z Re: How to get TOP 3 and Bottom 3 AVG and Max Difference value with rank function and AGGR. https://community.qlik.com/t5/QlikView/How-to-get-TOP-3-and-Bottom-3-AVG-and-Max-Difference-value-with/m-p/32765#M791021 <HTML><HEAD></HEAD><BODY><P>hi</P><P>look at this file</P></BODY></HTML> Thu, 01 Feb 2018 10:29:13 GMT https://community.qlik.com/t5/QlikView/How-to-get-TOP-3-and-Bottom-3-AVG-and-Max-Difference-value-with/m-p/32765#M791021 lironbaram 2018-02-01T10:29:13Z Re: How to get TOP 3 and Bottom 3 AVG and Max Difference value with rank function and AGGR. https://community.qlik.com/t5/QlikView/How-to-get-TOP-3-and-Bottom-3-AVG-and-Max-Difference-value-with/m-p/1956873#M1219941 <P>Hi I am unable to view this solution (QVW), could u post&nbsp; the logic in comments pls?</P> Mon, 18 Jul 2022 05:51:02 GMT https://community.qlik.com/t5/QlikView/How-to-get-TOP-3-and-Bottom-3-AVG-and-Max-Difference-value-with/m-p/1956873#M1219941 QS21 2022-07-18T05:51:02Z