Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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:
I can then set a fixed limitation of 4 on the dimension to get only the last 4 quarters to display:
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
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?
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?
Oh wow, that's the issue. Thank you so much!
No problem. Sometimes you just need a fresh pair of eyes