topic Re: Ranking Year and Quarter for 2 Different Dimensions in App Development https://community.qlik.com/t5/App-Development/Ranking-Year-and-Quarter-for-2-Different-Dimensions/m-p/1671469#M50783 <P>Is there anyway to make this a dimension so I could use it on the X axis in a line chart?</P> Fri, 31 Jan 2020 20:29:57 GMT gagewhite 2020-01-31T20:29:57Z Ranking Year and Quarter for 2 Different Dimensions https://community.qlik.com/t5/App-Development/Ranking-Year-and-Quarter-for-2-Different-Dimensions/m-p/1671391#M50770 <P>Hello,</P><P>I am trying to rank the most recent year and quarter for us and our competitors so I can show trends for the most recent four quarters of data, but cannot figure out how to make the ranking reset for each company. For example, this is what my data looks like before ranking:</P><TABLE width="561"><TBODY><TR><TD width="159">Company</TD><TD width="111">Company Owner</TD><TD width="81">Year</TD><TD width="113">Quarter</TD><TD width="97">Rank</TD></TR><TR><TD>Company 1</TD><TD>My Company</TD><TD>2019</TD><TD>1</TD><TD>&nbsp;</TD></TR><TR><TD>Company 1</TD><TD>My Company</TD><TD>2019</TD><TD>2</TD><TD>&nbsp;</TD></TR><TR><TD>Company 1</TD><TD>My Company</TD><TD>2019</TD><TD>3</TD><TD>&nbsp;</TD></TR><TR><TD>Company 1</TD><TD>My Company</TD><TD>2019</TD><TD>4</TD><TD>&nbsp;</TD></TR><TR><TD>Company 2</TD><TD>Competitor</TD><TD>2018</TD><TD>4</TD><TD>&nbsp;</TD></TR><TR><TD>Company 2</TD><TD>Competitor</TD><TD>2019</TD><TD>1</TD><TD>&nbsp;</TD></TR><TR><TD>Company 2</TD><TD>Competitor</TD><TD>2019</TD><TD>2</TD><TD>&nbsp;</TD></TR><TR><TD>Company 2</TD><TD>Competitor</TD><TD>2019</TD><TD>3</TD><TD>&nbsp;</TD></TR><TR><TD>Company 3</TD><TD>Competitor</TD><TD>2018</TD><TD>3</TD><TD>&nbsp;</TD></TR><TR><TD>Company 3</TD><TD>Competitor</TD><TD>2018</TD><TD>4</TD><TD>&nbsp;</TD></TR><TR><TD>Company 3</TD><TD>Competitor</TD><TD>2019</TD><TD>1</TD><TD>&nbsp;</TD></TR><TR><TD>Company 3</TD><TD>Competitor</TD><TD>2019</TD><TD>2</TD><TD>&nbsp;</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>As you can see, the most recent quarters:</P><UL><LI>Company 1: 2019 Q4</LI><LI>Company 2: 2019 Q3</LI><LI>Company 3: 2019 Q2</LI></UL><P>I would like the ranking to behave as followed:</P><TABLE width="561"><TBODY><TR><TD width="159">Company</TD><TD width="111">Company Owner</TD><TD width="81">Year</TD><TD width="113">Quarter</TD><TD width="97">Rank</TD></TR><TR><TD>Company 1</TD><TD>My Company</TD><TD>2019</TD><TD>1</TD><TD>4</TD></TR><TR><TD>Company 1</TD><TD>My Company</TD><TD>2019</TD><TD>2</TD><TD>3</TD></TR><TR><TD>Company 1</TD><TD>My Company</TD><TD>2019</TD><TD>3</TD><TD>2</TD></TR><TR><TD>Company 1</TD><TD>My Company</TD><TD>2019</TD><TD>4</TD><TD>1</TD></TR><TR><TD>Company 2</TD><TD>Competitor</TD><TD>2018</TD><TD>4</TD><TD>4</TD></TR><TR><TD>Company 2</TD><TD>Competitor</TD><TD>2019</TD><TD>1</TD><TD>3</TD></TR><TR><TD>Company 2</TD><TD>Competitor</TD><TD>2019</TD><TD>2</TD><TD>2</TD></TR><TR><TD>Company 2</TD><TD>Competitor</TD><TD>2019</TD><TD>3</TD><TD>1</TD></TR><TR><TD>Company 3</TD><TD>Competitor</TD><TD>2018</TD><TD>3</TD><TD>4</TD></TR><TR><TD>Company 3</TD><TD>Competitor</TD><TD>2018</TD><TD>4</TD><TD>3</TD></TR><TR><TD>Company 3</TD><TD>Competitor</TD><TD>2019</TD><TD>1</TD><TD>2</TD></TR><TR><TD>Company 3</TD><TD>Competitor</TD><TD>2019</TD><TD>2</TD><TD>1</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>This way, the most recent quarter for each company is the highest rank. I have tried aggregation on Year, Quarter, Company, and attempted many other things, but to no avail. Any input would be greatly appreciated!</P> Sat, 16 Nov 2024 03:23:51 GMT https://community.qlik.com/t5/App-Development/Ranking-Year-and-Quarter-for-2-Different-Dimensions/m-p/1671391#M50770 gagewhite 2024-11-16T03:23:51Z Re: Ranking Year and Quarter for 2 Different Dimensions https://community.qlik.com/t5/App-Development/Ranking-Year-and-Quarter-for-2-Different-Dimensions/m-p/1671445#M50778 <P>Here is a way to calculate the rank as you ask for I believe:</P><P>&nbsp;</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Annotation 2020-01-31 #1.png" style="width: 816px;"><img src="https://community.qlik.com/t5/image/serverpage/image-id/27825i1F62F8594CAAA09D/image-dimensions/816x314?v=v2" width="816" height="314" role="button" title="Annotation 2020-01-31 #1.png" alt="Annotation 2020-01-31 #1.png" /></span></P><P>The expression is:</P><P>Max(TOTAL &lt;Company&gt; Year*4+Quarter) - Max(TOTAL &lt;Year,Quarter&gt; Year*4+Quarter) + 1</P> Fri, 31 Jan 2020 19:10:06 GMT https://community.qlik.com/t5/App-Development/Ranking-Year-and-Quarter-for-2-Different-Dimensions/m-p/1671445#M50778 petter 2020-01-31T19:10:06Z Re: Ranking Year and Quarter for 2 Different Dimensions https://community.qlik.com/t5/App-Development/Ranking-Year-and-Quarter-for-2-Different-Dimensions/m-p/1671466#M50780 <P>Thank you so much! It works exactly as intended! I have never put values in the &lt;&gt; before, so I honestly have no clue why it is working, but it works.</P> Fri, 31 Jan 2020 20:19:10 GMT https://community.qlik.com/t5/App-Development/Ranking-Year-and-Quarter-for-2-Different-Dimensions/m-p/1671466#M50780 gagewhite 2020-01-31T20:19:10Z Re: Ranking Year and Quarter for 2 Different Dimensions https://community.qlik.com/t5/App-Development/Ranking-Year-and-Quarter-for-2-Different-Dimensions/m-p/1671469#M50783 <P>Is there anyway to make this a dimension so I could use it on the X axis in a line chart?</P> Fri, 31 Jan 2020 20:29:57 GMT https://community.qlik.com/t5/App-Development/Ranking-Year-and-Quarter-for-2-Different-Dimensions/m-p/1671469#M50783 gagewhite 2020-01-31T20:29:57Z Re: Ranking Year and Quarter for 2 Different Dimensions https://community.qlik.com/t5/App-Development/Ranking-Year-and-Quarter-for-2-Different-Dimensions/m-p/1672268#M50862 <P>Figured out a way to, essentially, turn this measure into a dimension in case anyone was curious:</P><P>Aggr(Max(TOTAL &lt;Company&gt; Year*4+Quarter) - Max(TOTAL &lt;Year,Quarter&gt; Year*4+Quarter) + 1, [Year], [Quarter], [Company])</P> Tue, 04 Feb 2020 17:47:05 GMT https://community.qlik.com/t5/App-Development/Ranking-Year-and-Quarter-for-2-Different-Dimensions/m-p/1672268#M50862 gagewhite 2020-02-04T17:47:05Z