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 :)
Quote from: badlad1990 on 20 Dec 2014 12:23:00 PM
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.
Quote from: badlad1990 on 21 Dec 2014 01:29:02 PM
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.