Data for last two months

[anirudh]

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!

[Francis aka khayman]

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]

[anirudh]

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!

[BigChris]

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

[anirudh]

Thank you so much Chris! Will try it out today and let you know.

[anirudh]

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.