COGNOiSe.com - The IBM Cognos Community

Someone knows how to write an expression for calculating two measures that are different from two different accounts?

Hi,

Can you be a bit more specific about what you are trying to achieve?  Are the account measures in separate columns of a query subject?  In separate rows of a query subject? In separate Query Subjects that do/do not have a relationship? In separate packages?  An example of what you need to do would be very helpful...

Regards,

MF.

Hello, I have a DataItem1 which is formed for various accounts with the measure actualf, I have DataItem2 which is formed by various accounts to the extent month, must add the total DataItem1 with Total DataItem2


Dataitem1
ACCOUNT                                  MEASURE(actualf)

account 1                                 100
account 2                                 150
account 3                                   10
TOTAL                                      260

Dataitem2
ACCOUNT                                  MEASURE(month)

account 1                                 50
account 2                                 70
account 3                                 20
TOTAL                                     140

DataItem3                               SUM(DataItem1 + DataItem2)
TOTAL                                      260 + 140 = 400

MY CASE IS SIMILAR TO THIS


"This is my question.

For example:
Query 1
Row1Ã,  Ã,  Ã, 20
Row2Ã,  Ã,  Ã, 22
Row3Ã,  Ã,  Ã, 23
Row4Ã,  Ã,  Ã, 30
Row5Ã,  Ã,  Ã, 31
Row6Ã,  Ã,  Ã, 32
Row7Ã,  Ã,  Ã, 33
TOTAL1 191

Query 2
Row1Ã,  Ã,  Ã, 33
Row2Ã,  Ã,  Ã, 32
Row3Ã,  Ã,  Ã, 31
Row4Ã,  Ã,  Ã, 30
Row5Ã,  Ã,  Ã, 23
TOTAL2 149

TOTAL1-TOTAL2 = 42

But I wasnÃ,´t able to mix those calculations (total1 - total2) because I have them in different queries.

Is there any way to make calculations with items from different queries...?"

Hi,

Rather than coding this using two queries, you may be able to do it in one, by conditionally hiding the column values for the rows outside the top 5.

Step 1: Bring all the rows into a query, add a calculation to rank the measure values  - rank ([Measure]) then add a second calculation that has the following expression - if ([your rank calculation] <= 5) then ('best') else ('others').  Group your report on the new calculation, and add summary footers for your measure values using the aggregate button.

Step 2: Define a boolean variable in the condition explorer with the expression [your second calculation] = 'others'.  Select the list column bodies in your list, and assign your variable to the conditional style property.  Hover over the condition explorer and select the 'yes' variable value, then set the box type property of the list column bodies to be 'none' for this variable value.

You may need to render as PDF to get this to display as you would wish, but it may be an easier option than trying to use multiple queries.

Regards,

MF.

I managed not pass the problem!
I can bring the value that must, in DataItem separately. I can not calculate two values that contains different measure, I can calculate values with the same measure.