find variance of dynamic Year column

[ldm]

Hi All!
I need help.  I have been at this report for days and I am coming down to the wire and decided to turn to you all for help!!
To illustrate my issue, I recreated the issue using the Go Data Warehouse Cognos sample package.  I understand using an olap source would be preferable for this type of analysis but we just don't have that at this time.

Anyone have a solution on how I can dynamically calculate the change over the year chosen in the prompt vs. it's prior year?

Much appreciation to a solution!!!  OR even a bit of guidance!!!

[CognosPaul]

Try setting your filter to:

  [Year] in (?Year?,?Year?-1)

And your measures to (for example):
Sales Current Year:
  case when [Year] = ?Year? then [NS].[Measures].[Sales] else 0 end
Sales Previous Year:
  case when [Year] = ?Year? - 1 then [NS].[Measures].[Sales] else 0 end
Variance (with solve order set to 2, and aggregation to calculated):
[Sales Current Year] / [Sales Previous Year] - 1

There are a number of other ways to do it, you could try setting up a DMR solution for example. This way is the easiest to understand though.

[ldm]

Thank you Paul!! I will try that and let you know how I made out.