Difference between calculated data items

[cognos4321]

Hi all,

I have a crosstab report in which there are 2 calculated columns.
Now I need to find the difference(variance) between them.
I can't use tuple function since tuple doesn't work with calculated data items.
How do I achieve this?
Please help.

I am working on cognos 10.1 , dimensional model sourcing from TM1 cubes.


Thanks a lot.

[cognos4321]

Hi ,


Is my question weird or I need to provide more details.
Or is it just not possible to find the difference between 2 calculated columns(data items) dragged in a crosstab report.


Thanks in advance.

[CognosPaul]

[Data item1] - [Data item2] doesn't work?

[cognos4321]

No, it doesn't because the columns in the crosstab look like (nested )

Actual  (selected scenario)           Budget (selected comparison scenario)
2016   (selected year)                  2015 (selected comparison year)
QTD selected month                     QTD selected comparison month

This QTD has acondition that if you select Jan it will show Jan, if Feb then 'Jan + Feb', if Mar then 'Jan+Feb+Mar'.


Now for Variance column I have put the condition using tuples(below) but I can't include this QTD in my tuple condition below because it's calculated data item and gives me error.
Only because of this QTD calulated data item I can't get the variance of the above shown nested columns in the crosstab.

I have put this condition for Variance column

IF (Value (Tuple([Actual], [Selected_Year],[Amount] ) )  is not null or Value (Tuple(  [Selected_Scenario],[Selected_Comparison_Year]  ,[Amount] ) ) is not null  )
then (
Value (Tuple([Actual], [Selected_Year] ,[Amount] ) )  -
Value (Tuple( [Selected_Scenario],[Selected_Comparison_Year]  ,[Amount]) )
)
Else ( IF (Value (Tuple( [Actual],[Selected_Year]  , [Amount]) ) is null)
Then ( Value (Tuple( [Selected_Scenario], [Selected_Comparison_Year] , [Amount])) *-1  )
Else ( Value (Tuple( [Actual], [Selected_Year] , [Amount]) ) ) )



Please let me know if I need to provide further information.

Thank you for your help.