Hello Cognos gurus!
My report has P_Year and P_Month as input parameters. (The P_Month prompt is cascaded from the P_Year prompt). I have a Revenue column that populates for this combination of Month and Year, for every executive.
Now I need the same Revenue column value for the last two months based on the input parameter.
For example: If my input parameters are 2014 & March, my result set currently looks like this:
Exec Revenue for (March 2014)
A 1111
B 2222
C 3333
How I want it to look like is this:
Exec Revenue for (March 2014) Revenue for (Feb 2014) Revenue for (Jan 2014)
A 123131 876876 876834
B 908080 821234 768688
C 954262 121333 231313
Any help on this would be greatly appreciated!
Thanks in advance!
are you using dimensional or relational?
oh well...
dimensional... you can use set([chosen month],lag([chosen month],1),lag([chosen month],2)) to get the three months
relational depending on the key you use you might need to write something like:
Given:
Month-Year Key = 201401, 201402, 201403, etc...
?year? * 100 + ?month? between [year] * 100 + [month] - 3 and [year] * 100 + [month]
Thanks a lot for your response Khayman. I am using a relational model and I need to put these columns by month in a list.
Also, I do not have a key value. The months come in from 1 to 12 and the years from 2010 to 2015.
Can you please tell me how I can have the data for the last two months as two separate columns?
Once again.. Thanks for your time!
Ok, there are probably better ways to do this, but this is the sort of approach I'd take
Your first cloumn, Exec, stays the same.
Your second column becomes:
if([Month] = p_Month and [Year] = p_Year) then ([Revenue]) else (0)
That's the easy part..now you've got to take into account the end of the year and the month that you select...
if([Month] = (case p_Month when 1 then 12 else p_month -1 end) and [Year] = (case when p_Month = 1 then p_Year -1 else p_Year end)) then ([Revenue]) else (0)
if([Month] = (case p_Month when 1 then 11 when 2 then 12 else p_month -2 end) and [Year] = (case when p_Month < 3 then p_Year -1 else p_Year end)) then ([Revenue]) else (0)
Obviously you'll need to check that...I've just written it off the top of my head so there's likely to be all sorts of typoes in there. I also imagine you'll want to aggregate, so you'll need put totals in etc.
Anyway, give it a go and see where you get to...
C
Thank you so much Chris! Will try it out today and let you know.
Thanks Chris, I was able to make this logic work by using them as filters. I created two new queries (one for last month and one for a month before that), used your logic for month and year as filters on these queries and did a union on them. Worked out just fine!
Thanks a lot for this.