calculate rank for current and priror year

[badlad1990]

Hi guys , im new to cognos and have been trying to calculate rank for the current year and prior year based on gross profit in the report studio.

Thanks :)

[MFGF]

Hi guys , im new to cognos and have been trying to calculate rank for the current year and prior year based on gross profit in the report studio.

Thanks :)

Hi,

Are you using a dimensional or a relational package? The approach you take to achieve your requirement will differ drastically depending on the answer.

MF.

[badlad1990]

it is a relational package :)

[MFGF]

it is a relational package :)

Hi,

In that case, you're going to need to create calculations for This Year Gross Profit and Last Year Gross Profit, and rank these.

Assuming you already have a list report containing the items you want to rank on, add a query calculation from the toolbox to your list. Call it 'Gross Profit TY' and create the expression as:
if (<your year item from the package> = extract(year, current_date)) then (<your Gross Profit item from the package>) else (0)
Note - it is important to bring in both Year and Gross Profit from the package into your expression - do not bring them in from the current query, as this affects the timing of the calculation.
Add a second Query Calculation called 'Gross Profit LY' with the expression:
if (<your year item from the package> = extract(year, current_date) - 1) then (<your Gross Profit item from the package>) else (0)
Now add another query calculation from the toolbox. Call this one 'Rank TY' and use the expression:
rank(<your Gross Profit TY item from the query>)
Add a final query calculation called Rank LY with the expression:
rank(<your Gross Profit LY item from the query>)

Cheers!

MF.