Good afternoon Cognos Gurus,
I have a List Report with the following structure:
Year | Product | Current Month # | Prior Month # | Difference #
Year and Product are grouped and Difference is Current Month - Prior Month.
I need to return the Top 20 Years where the difference is the smallest (top negative values). So the Top 20 would be the summation of the difference column BY Year, and then return the Top 20.
I am using a relational model.
Any help would be greatly appreciated.
Best,
Zach George
Quote from: georgezmCognos on 11 Nov 2022 11:02:58 AM
Good afternoon Cognos Gurus,
I have a List Report with the following structure:
Year | Product | Current Month # | Prior Month # | Difference #
Year and Product are grouped and Difference is Current Month - Prior Month.
I need to return the Top 20 Years where the difference is the smallest (top negative values). So the Top 20 would be the summation of the difference column BY Year, and then return the Top 20.
I am using a relational model.
Any help would be greatly appreciated.
Best,
Zach George
Hi,
To get the sum of the difference by year, you could either use total([Difference] for [Year]), or you could use total ([Current Month] for [Year]) - total([Prior Month] for [Year])
You can then use a rank function to rank them in descending order
rank([Your Year Difference Item above] DESC)
Finally you can filter on the rank item so you just get the top 20
Cheers!
MF.
Just to add to what MFGF said - The filter on Rank would be a summary filter, not a detailed filter.