Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
gagewhite
Contributor III
Contributor III

Ranking Year and Quarter for 2 Different Dimensions

Hello,

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:

CompanyCompany OwnerYearQuarterRank
Company 1My Company20191 
Company 1My Company20192 
Company 1My Company20193 
Company 1My Company20194 
Company 2Competitor20184 
Company 2Competitor20191 
Company 2Competitor20192 
Company 2Competitor20193 
Company 3Competitor20183 
Company 3Competitor20184 
Company 3Competitor20191 
Company 3Competitor20192 

 

As you can see, the most recent quarters:

  • Company 1: 2019 Q4
  • Company 2: 2019 Q3
  • Company 3: 2019 Q2

I would like the ranking to behave as followed:

CompanyCompany OwnerYearQuarterRank
Company 1My Company201914
Company 1My Company201923
Company 1My Company201932
Company 1My Company201941
Company 2Competitor201844
Company 2Competitor201913
Company 2Competitor201922
Company 2Competitor201931
Company 3Competitor201834
Company 3Competitor201843
Company 3Competitor201912
Company 3Competitor201921

 

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!

Labels (3)
1 Solution

Accepted Solutions
petter
Partner
Partner

Here is a way to calculate the rank as you ask for I believe:

 

Annotation 2020-01-31 #1.png

The expression is:

Max(TOTAL <Company> Year*4+Quarter) - Max(TOTAL <Year,Quarter> Year*4+Quarter) + 1

View solution in original post

4 Replies
petter
Partner
Partner

Here is a way to calculate the rank as you ask for I believe:

 

Annotation 2020-01-31 #1.png

The expression is:

Max(TOTAL <Company> Year*4+Quarter) - Max(TOTAL <Year,Quarter> Year*4+Quarter) + 1

View solution in original post

gagewhite
Contributor III
Contributor III
Author

Thank you so much! It works exactly as intended! I have never put values in the <> before, so I honestly have no clue why it is working, but it works.

gagewhite
Contributor III
Contributor III
Author

Is there anyway to make this a dimension so I could use it on the X axis in a line chart?

gagewhite
Contributor III
Contributor III
Author

Figured out a way to, essentially, turn this measure into a dimension in case anyone was curious:

Aggr(Max(TOTAL <Company> Year*4+Quarter) - Max(TOTAL <Year,Quarter> Year*4+Quarter) + 1, [Year], [Quarter], [Company])