If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Cognos Rank Function

Started by georgezmCognos, 11 Nov 2022 11:02:58 AM

Previous topic - Next topic

georgezmCognos

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

MFGF

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.
Meep!

cognostechie

Just to add to what MFGF said - The filter on Rank would be a summary filter, not a detailed filter.