I have a financial report I am trying to build. I simply want to calculate a gross profit. I have a field called type that contain records for revenue and expenses. I can't figure out how to subtract expenses from revenue since they are in the same field. How can I accomplish this?
You will need two calculations - one for expenses and one for revenue...
This would be the Expenses one:
if ([type] = 'Expenses') then ([Your measure]) else (0)
Create a similar calc for Revenue, then you can subtract one from the other in your Gross Profit calc.
Cheers!
MF.
I'm completely new to Cognos. How do I go about doing that?
From the toolbox, drag a Query Calculation into your crosstab. Name it Expenses. For the expression, build the syntax I included in the previous post. The items such as [Type] and [Your measure] can be dragged in from the package or from the existing query, and you can type in the rest.
Repeat the exercise to create another calculation called Revenue. Create the same expression, apart from it saying = 'Revenue'
Finally drag in another query calculation, name it Gross Profit, and for the expression pick your Revenue calc from the query, type in a minus sign, then drag in the Expenses item
ie [Revenue] - [Expenses]
MF.
This works. Thank You....