Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I've been beating my head against this for the past week and I'm getting closer to my solution. I am trying to find out a monthly total and spread it across that many months based on:
Implementation Date - Which has an alliance to my master calendar date
$(eTotalSavings) - Which is SUM(FY2000+FY2001+...FY2020)
CalcMonth - Which is PICK(MATCH(CalendarMonthName, 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'), 6, 7, 8, 9, 10, 11, 12, 1, 2, 3, 4, 5) as CalcMonth
MonthNameandYear - Which is MonthName(TempDate) as CalendarMonthAndYear,
I created a variable for monthly total which is $(eTotalSavings/CalcMonth)
Now I have my monthly total in a pivot table table, which displays the following, but I want the monthly savings to spread across Implementation Date Start month + CalcMonth. So Jul should spread across 12 months while August 1 month and September 2 months ect how can this be accomplished with Set Analysis?
Date | CalendarMonthAndYear | Jul 2010 | Aug 2010 | Sep 2010 | Oct 2010 | Nov 2010 | Dec 2010 | Jan 2011 | Feb 2011 | Mar 2011 | Apr 2011 | May 2011 | Jun 2011 |
7/1/2010 | 2220983.61 | - | - | - | - | - | - | - | - | - | - | - | |
8/1/2010 | - | 7423377.38 | - | - | - | - | - | - | - | - | - | - | |
9/1/2010 | - | - | 1034323.97 | - | - | - | - | - | - | - | - | - | |
10/1/2010 | - | - | - | 2582659.997 | - | - | - | - | - | - | - | - | |
11/1/2010 | - | - | - | - | 630290.82 | - | - | - | - | - | - | - | |
12/1/2010 | - | - | - | - | - | 631737.664 | - | - | - | - | - | - | |
1/1/2011 | - | - | - | - | - | - | 1481003.16 | - | - | - | - | - | |
1/11/2011 | - | - | - | - | - | - | 33351.47333 | - | - | - | - | - | |
2/1/2011 | - | - | - | - | - | - | - | 811157.6257 | - | - | - | - | |
3/1/2011 | - | - | - | - | - | - | - | - | 718391.0425 | - | - | - | |
3/31/2011 | - | - | - | - | - | - | - | - | 4828.12125 | - | - | - | |
4/1/2011 | - | - | - | - | - | - | - | - | - | 1235129.877 | - | - | |
5/1/2011 | - | - | - | - | - | - | - | - | - | - | 298935.66 | - | |
6/1/2011 | - | - | - | - | - | - | - | - | - | - | - | 1335653.623 |
Is it possible to post a sample application?