Cognos Calculate Variance Crosstab (Dimensional)

Question

This is very similar to Cognos Calculate Variance Crosstab (Relational), but my data source is dimensional.

I have a simple crosstab such as this:

          | 04-13-2013 | 04-13-2014  
---------------------------------------
Sold      |   75         |  50   
Purchased |   10         |  15   
Repaired  |   33         |  44  

• Filter: The user selects 1 date and then we include that date plus 1 year ago.
• Dimension: The date is the day level in a YQMD Hierarchy.
• Measures: We are showing various measures from a Measure Dimension.
  • Sold
  • Purchased
  • Repaired

Here is what is looks like in report studio:

                  | <#Day#>   | <#Day#> 
---------------------------------------
   <#Sold#>       |  <#1234#> |  <#1234#> 
   <#Purchased#>  |  <#1234#> |  <#1234#> 
   <#Repaired#>   |  <#1234#> |  <#1234#> 

I want to be able to calculate the variance as a percentage between the two time periods for each measure like this.

          | 04-13-2013 | 04-13-2014  |  Var. %
-----------------------------------------------
Sold      |   75         |  50       |  -33%
Purchased |   10         |  15       |   50%
Repaired  |   33         |  44       |   33%

I added a Query Expression to the right of the <#Day#> as shown below, but I cannot get the variance calculation to work.

                  | <#Day#>   | <#Variance#> 
---------------------------------------
   <#Sold#>       |  <#1234#> |  <#1234#> 
   <#Purchased#>  |  <#1234#> |  <#1234#> 
   <#Repaired#>   |  <#1234#> |  <#1234#> 

These are the expressions I've tried and the results that I get:

An expression that is hard coded works, but only for that 1 measure:

total(case when [date] = 2014-04-13 then [Sold] end)
/
total(case when [date] = 2013-04-13 then [Sold] end)
-1

I thought CurrentMember and PrevMember might work, but it produces blank cells:

CurrentMember( [YQMD Hierarchy] ) 
/
prevMember(CurrentMember([YQMD Hierarchy]))
-1

I think it is because prevMember produces blank.

prevMember(CurrentMember([YQMD Hierarchy]))

Using only CurrentMember gives a total of both columns:

CurrentMember([YQMD Hierarchy]) 

What expression can I use to take advantage of my dimensional model and add a column with % variance?

These are the pages I used for research:

• Variance reporting in Report Studio on Cognos 8.4?
• Calculations that span dimensions - PDF
• IBM Cognos 10 Report Studio: Creating Consumer-Friendly Reports

Answer 1

I hope there is a better way to do this. I finally found a resource that describes one approach to this problem. Using the tail and head functions, we can get to the first and last periods, and thereby calculate the % variance.

item(tail(members([Day])),0)
/
item(head(members([Day])),0)
-1

This idea came from IBM Cognos BI – Using Dimensional Functions to Determine Current Period.

Example 2 – Find Current Period by Filtering on Measure Data

If the OLAP or DMR data source has been populated with time periods into the future (e.g. end of year or future years), then the calculation of current period is more complicated. However, it can still be determined by finding the latest period that has data for a given measure.

item(tail(filter(members([sales_and_marketing].[Time].[Time].[Month]),
 tuple([Revenue], currentMember([sales_and_marketing].[Time].[Time]))
 is not null), 1), 0)