Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a data source like the following:
Date | FY Total | # of Months | Monthly Total |
7/1/2010 | $2,220,984 | 12 | $185,081.97 |
8/1/2010 | $7,423,377 | 1 | $7,423,377.38 |
9/1/2010 | $1,034,324 | 2 | $517,161.99 |
10/1/2010 | $2,582,660 | 3 | $860,886.67 |
11/1/2010 | $630,291 | 4 | $157,572.71 |
12/1/2010 | $631,738 | 5 | $126,347.53 |
1/1/2011 | $1,481,003 | 6 | $246,833.86 |
1/11/2011 | $33,351 | 6 | $5,558.58 |
2/1/2011 | $811,158 | 7 | $115,879.66 |
3/1/2011 | $718,391 | 8 | $89,798.88 |
3/31/2011 | $4,828 | 8 | $603.52 |
4/1/2011 | $1,235,130 | 9 | $137,236.65 |
5/1/2011 | $298,936 | 10 | $29,893.57 |
6/1/2011 | $1,335,654 | 11 | $121,423.06 |
I want to display it like this based on the number of months (divide the yearly total by number of months to get the monthly total and spread it across the months.
Date | Jul 2010 | Aug 2010 | Sep 2010 | Oct 2010 | Nov 2010 | Dec 2010 | Jan 2011 | Feb 2011 | Mar 2011 | Apr 2011 | May 2011 | Jun 2011 | FY Total | # of Months | Monthly Total |
7/1/2010 | $185,082 | $185,082 | $185,082 | $185,082 | $185,082 | $185,082 | $185,082 | $185,082 | $185,082 | $185,082 | $185,082 | $185,082 | $2,220,984 | 12 | $185,082 |
8/1/2010 | $7,423,377 | - | - | - | - | - | - | - | - | - | - | $7,423,377 | 1 | $7,423,377 | |
9/1/2010 | $517,162 | $517,162 | - | - | - | - | - | - | - | - | - | - | $1,034,324 | 2 | $517,162 |
10/1/2010 | $860,887 | $860,887 | $860,887 | - | - | - | - | - | - | - | - | - | $2,582,660 | 3 | $860,887 |
11/1/2010 | $157,573 | $157,573 | $157,573 | $157,573 | - | - | - | - | - | - | - | - | $630,291 | 4 | $157,573 |
12/1/2010 | $126,348 | $126,348 | $126,348 | $126,348 | $126,348 | - | - | - | - | - | - | - | $631,738 | 5 | $126,348 |
1/1/2011 | $246,834 | $246,834 | $246,834 | $246,834 | $246,834 | $246,834 | - | - | - | - | - | - | $1,481,003 | 6 | $246,834 |
1/11/2011 | $5,559 | $5,559 | $5,559 | $5,559 | $5,559 | $5,559 | - | - | - | - | - | - | $33,351 | 6 | $5,559 |
2/1/2011 | $115,880 | $115,880 | $115,880 | $115,880 | $115,880 | $115,880 | $115,880 | - | - | - | - | - | $811,158 | 7 | $115,880 |
3/1/2011 | $89,799 | $89,799 | $89,799 | $89,799 | $89,799 | $89,799 | $89,799 | $89,799 | - | - | - | - | $718,391 | 8 | $89,799 |
3/31/2011 | $604 | $604 | $604 | $604 | $604 | $604 | $604 | $604 | - | - | - | - | $4,828 | 8 | $604 |
4/1/2011 | $137,237 | $137,237 | $137,237 | $137,237 | $137,237 | $137,237 | $137,237 | $137,237 | $137,237 | - | - | - | $1,235,130 | 9 | $137,237 |
5/1/2011 | $29,894 | $29,894 | $29,894 | $29,894 | $29,894 | $29,894 | $29,894 | $29,894 | $29,894 | $29,894 | - | - | $298,936 | 10 | $29,894 |
6/1/2011 | $121,423 | $121,423 | $121,423 | $121,423 | $121,423 | $121,423 | $121,423 |
See if attached is what you are looking for. It was a bit tricky, but this is the formula I used for expression:
if(if(max(total <Date>aggr($(eMonthlySavings)/CalcMonth, Date)) <> 0,mod(num(month(CalendarMonthAndYear))+5, 12)+1) <= max(total <Date> aggr(CalcMonth, Date)), max(total <Date>aggr($(eMonthlySavings)/CalcMonth, Date)))
This might not be the most optimized formula, but it works.
Hope this helps!
See if attached is what you are looking for. It was a bit tricky, but this is the formula I used for expression:
if(if(max(total <Date>aggr($(eMonthlySavings)/CalcMonth, Date)) <> 0,mod(num(month(CalendarMonthAndYear))+5, 12)+1) <= max(total <Date> aggr(CalcMonth, Date)), max(total <Date>aggr($(eMonthlySavings)/CalcMonth, Date)))
This might not be the most optimized formula, but it works.
Hope this helps!