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!
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