How do I group some members but leave the others as well for measure?

[rteruyas]

Hello guys, I have a request to show current year, last year and avg of 5 past years as 3 columns. I've been trying to get it to work with no success so I come here in search from some help
I'm using a dimensional model

Data sample:

Year | Week | Price
2011 - 01 - 20.3
2011 - 02 - 21.0
....
....
2011 - 52 - 18.4
2012 - 01 - 18.5
2012 - 02 - 19.7
....
2017 - 13 - 20.3

Expected results

Week | 2011to2015 | 2016 | 2017                             
1       |    Avg Price (2011-2015) week1  | 2016 week1 price | 2017 week1 price
2       |    Avg Price (2011-2015) week2   | 2016 week2 price | 2017 week2 price
3       |    Avg Price (2011-2015) week3   | 2016 week3 price | 2017 week3 price
.....
52       |    Avg Price (2011-2015) week52   | 2016 week52 price | 2017 week52 price

The request is also to show it in a curve, one line for 2017, one for 2016 and one for [2011-2015]
Any thoughts will be much appreciated, thanks!

[Marvin Falentin]

Hey,

I thought about your problem and one thing came to my mind:

Can you set up a new query subject in your model consisting of e.g. "week" "price" and "year11-15", "year16" and "year17". While "week" and "price" are a copy from your datasource you would create three "years", call one something like "year17" and define in the query subject something like "[Namespace].[QuerySubject].[Year] = '2017'.

Once you set up your QS you can use it in your Report or Dashboard or whatever you're building as a query without using a filter (which would be applied to all of the query).

I'm by no means a Cognos Framework Manager expert and I didn't try that approach but I can imagine it to be working, maybe you just give it a try and tell us how it worked out? After all you said any suggestion would be appreciated :)

Kind regards,

Marvin