Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gagewhite
Contributor III
Contributor III

Ranking Year and Quarter as Dimension for Last 4 Quarters

Hello,

I am trying to compare quarterly competitor financial data with my company's financial data on a line chart for the most recent 4 quarters. Due to the varying fiscal calendars, us and our competitors are commonly on different quarters. So I ranked the year and quarter as a measure in a table using -Rank(TOTAL CONCAT([Posting Year], [Posting Quarter]),0,0) in order to compare the four most recent quarters regardless of what quarter each company is in. I made the rank a negative so the quarters line up chronologically on a line chart. Currently, I have 8 quarters of data for our company, but only want to display the four most recent quarters. Here is a picture of the table:

Quarterly Table.PNG

 

Next, I wanted to turn this into a line chart. Here are the columns for the line chart:

Dimension: =Aggr(-Rank(TOTAL CONCAT([Posting Year], [Posting Quarter]),0,0),[Posting Year], [Posting Quarter])

Measure: =If(-Rank(TOTAL CONCAT([Posting Year], [Posting Quarter]),0,0) >= -4, Sum({$<[Statement Lines] = {'Operating Income'}>}[Transaction Amount - USD]))

Qlik will not let me use rank by itself as a dimension, so I aggregated it on Posting Year & Posting Quarter.. Here is a picture of the line chart:

Quarterly Line Chart.PNG

 

I can then set a fixed limitation of 4 on the dimension to get only the last 4 quarters to display:

Quarterly Line Chart Last 4.PNG

 

However, the issue is that the monetary values on the line chart do not match the monetary values on the table (the table has the correct values). I believe there is an issue with the dimension in my line chart. I have spent longer than I care to admit trying to solve this issue, and for some reason I feel like the solution is much more simple than I am trying to make it. Any help would be greatly appreciated!

Thanks,

Gage

Labels (4)
1 Solution

Accepted Solutions
lorenzoconforti
Specialist II
Specialist II

Let's start with the measure; in the table on top you have:

[Transaction Amount - Constant Currency]

while on the line chart you have:

[Transaction Amount - USD]

 

Is that correct?

View solution in original post

3 Replies
lorenzoconforti
Specialist II
Specialist II

Let's start with the measure; in the table on top you have:

[Transaction Amount - Constant Currency]

while on the line chart you have:

[Transaction Amount - USD]

 

Is that correct?

gagewhite
Contributor III
Contributor III
Author

Oh wow, that's the issue. Thank you so much!

lorenzoconforti
Specialist II
Specialist II

No problem. Sometimes you just need a fresh pair of eyes