Cognos Calculate Variance Crosstab (Relational)

Question

I have a simple crosstab such as this:

Trans     |    Pants    |    Shirts   |
          | 2013 | 2014 | 2013 | 2014 |
---------------------------------------
Jan       | 33   |  37  |  41  |  53  |
Feb       | 31   |  33  |  38  |  43  |
Mar       | 26   |  29  |  51  |  56  |

• Pants and Shirt belong to the data item: Category

• Years belong to the data item: Years

• Months belong to the data item: Months

• Trans (transactions) belongs to the data item: Trans

Here is what is looks like in report studio:

Trans     |      <#Category#>     |      <#Category#>     |
          | <#Years#> | <#Years#> | <#Years#> | <#Years#> |
-----------------------------------------------------------
<#Months#>|  <#1234#> |  <#1234#> | <#1234#>  | <#1234#>  |

I want to be able to calculate the variance of pants and shirts between the years. To get something like this:

Trans     |           Pants            |           Shirts           |
          | 2013 | 2014 | YOY Variance | 2013 | 2014 | YOY Variance |
---------------------------------------------------------------------
Jan       | 33   |  37  |     12.12    | 41   |  53  |     29.27    |
Feb       | 31   |  33  |     6.45     | 38   |  43  |     13.16    |
Mar       | 26   |  29  |     11.54    | 51   |  56  |     9.80     |

I've tried inserting a data item for YOY Variance with the expression below just to see if I can even get the 2014 value but cannot, some odd reason it only returns the 2013 values:

Total([Trans] for maximum[Year],[Category],[Months])

Any ideas? Help?

Answer 1

(I'm assuming you don't have a DMR.)

There is no easy/clean way to do this in Cognos. In your query, you'll have to build a calculation for each year in your output. So, something like this for 2013:

total (if [Years] = 2013) then ([Trans]) else (0))

And basically the same for 2014.

Cut the Trans piece out of your crossab. Then you'll nest those two calcs under your years. To get rid of all the zeroes or nulls, select the two columns. From the menu, select Data, Suppress,Suppress Columns Only.

Finally, you will drop a calc in next to your Years in the crosstab (not under them). The expression will be ([2014 trans] - [2013 trans])/[2014 trans] (or whatever you end up naming your calcs). Format it as a percent, and you should be good to go.

Told you it was a pain!