Projection (Forecast) Calculation

[leahmarie]

Hey guys,

I am attempting to do a calculation based on previous years data to forecast the number this year and next year.  They want to see it in a chart, but I have been trying to do it in a crosstab first to make it work.  I have tried currentMember-prevMember/prevMember to get the percentage and that seems to work, which then I am trying to multiply that percentage change by month to the month last year.  In case that's confusing:

                       2009     2010          (2011)=x   
January            100       120         (((120-105)/105)*116)+116   
February          110        125       
March               108        118       
April                 105         116     


So I have the first part the 120-105/105 but I can't get it to multiply by the last value in 2010 to get January for 2011, and yes I know it is August and we have actual results for January, but I need an entire forecasted line.  Then the value I get for January will be used to multiply and add in February.  This is the simplest forecasting calculation I can come up with, since there is only one variable.  If there was another variable I could use regression, and a slope function doesn't make much sense either.  Given even if I were trying to use a slope function, other than hardcoding, I don't know of a way to do that in Cognos either.  If anyone has any suggestions at all they would be greatly appreciated:)

Thanks so much.   

[leahmarie]

Oh, and I also tried using an average function to calculate the percent change and I couldn't make that work either.  I should mention as well that the hierarchy is weird and in order to get the crosstab and chart to have January on the x axis or rows and 2010 on the y axis or columns, I had to make a calculated member for the years that involved 2010 January+2010 February and so on.  The Month hierarchy that I used on the x axis does not take measures well and therefore caused a ton of problems, and our developers cannot figure out a way to fix it.  Therefore I had to use MUN's.  Which I know is frowned upon, and also annoying for me because I have to update the charts every month.

[tosunko]

can you please show me how you created the percentages in crosstab? I am currently a report where I need to see the percentage change from year to year.

Thanks

[MFGF]

can you please show me how you created the percentages in crosstab? I am currently a report where I need to see the percentage change from year to year.

Thanks

Are you using a relational or dimensional package? It makes a big difference to the approach you would use...

Cheers!

MF.