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.
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.
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
Quote from: tosunko on 05 Aug 2014 02:11:18 PM
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.