14 Projected Year End Giving Shared by Kevin Rutledge, Kevin Rutledge one year ago 7.0 Administration / Finance Beginner A few years back, I participated in a multi-year church finance training that touched on financial reports, stewardship, and legacy giving. During the course, I received a spreadsheet that let you enter giving totals by the month for the last three years. Using those numbers, it creates a three year total and the monthly proportion of that total. Using that % value, you can enter in the giving for the current year and calculate a year-end estimate of giving. I recreated that worksheet to use sql to pull giving data by month of selected accounts, present the information in tables, show a year-end estimate, and graph the data on a chart. This sql is currently set up to look at account #1, and all transactions before the start of the current month(I did not want partial months to skew the results whenever the page loads. You can change the account list in two places and change the start date in one place if you don't want these limitations.The screenshot below is from the rock demo taken in January so I removed the "before previous month" argument. Ingredients Dynamic Data Block Query: Select Cast([Year] as VarChar(8)) as Year ,[January] ,[February] ,[March] ,[April] ,[May] ,[June] ,[July] ,[August] ,[September] ,[October] ,[November] ,[December] , ( ISNULL([January], 0) +ISNULL([February], 0) +IsNULL([March], 0) +IsNULL([April], 0) +IsNULL([May], 0) +IsNULL([June], 0) +IsNULL([July], 0) +IsNULL([August], 0) +IsNULL([September], 0) +IsNULL([October], 0) +IsNULL([November], 0) +IsNULL([December], 0) ) as Total Into #tempdata FROM ( Select DatePart(Year,ft.TransactionDateTime) as Year ,DateName(Month,ft.TransactionDateTime) as [Month] ,ftd.Amount From FinancialTransaction ft left Join FinancialTransactionDetail ftd on ftd.TransactionId = ft.Id left Join FinancialAccount fa on fa.Id = ftd.AccountId where fa.Id in(1) and TransactionDateTime > '01/01/{{'Now' | Date:'yyyy' | Minus:3}}' and TransactionDateTime < '{{'Now' | Date:'MM/01/yyyy'}}' -- Remove this line if you want to see giving through the current time. ) as src Pivot ( Sum(Amount) For [Month] In ([January] ,[February] ,[March] ,[April] ,[May] ,[June] ,[July] ,[August] ,[September] ,[October] ,[November] ,[December]) ) as pvt ; --- Total over Years WITH data_test ([Year],January, February, March, April, May, June, July, August, September, October, November, December, Total) AS ( Select [Year] ,[January] ,[February] ,[March] ,[April] ,[May] ,[June] ,[July] ,[August] ,[September] ,[October] ,[November] ,[December] , (ISNULL([January], 0) +ISNULL([February], 0) +IsNULL([March], 0) +IsNULL([April], 0) +IsNULL([May], 0) +IsNULL([June], 0) +IsNULL([July], 0) +IsNULL([August], 0) +IsNULL([September], 0) +IsNULL([October], 0) +IsNULL([November], 0) +IsNULL([December], 0)) as Total FROM ( Select DatePart(Year,ft.TransactionDateTime) as Year ,DateName(Month,ft.TransactionDateTime) as [Month] ,ftd.Amount ,fa.Name From FinancialTransaction ft left Join FinancialTransactionDetail ftd on ftd.TransactionId = ft.Id left Join FinancialAccount fa on fa.Id = ftd.AccountId where fa.Id in(1) and TransactionDateTime > '01/01/{{'Now' | Date:'yyyy' | Minus:3}}' and TransactionDateTime < '{{'Now' | Date:'yyyy'}}' ) as src Pivot ( Sum(Amount) For [Month] In ([January] ,[February] ,[March] ,[April] ,[May] ,[June] ,[July] ,[August] ,[September] ,[October] ,[November] ,[December]) ) as pvt ) Insert Into #tempdata Select 'Total' as [Year] ,Sum(January) as January ,Sum(February) as February ,Sum(March) as March ,Sum(April) as April ,Sum(May) as May ,Sum(June) as June ,Sum(July) as July ,Sum(August) as August ,Sum(September) as September ,Sum(October) as October ,Sum(November) as November ,Sum(December) as December ,Sum(Total) as Total From data_test ------------- Select * from #tempdata Order By Year drop table #tempdata Formatted Output <script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.7.1/Chart.bundle.min.js"> </script> <style> .year { text-align:center; } .yeartables >div:nth-child(-n+2) { margin-right:12.5%; padding-right: 20px; } </style> {% assign 3yearsback = 'Now' | DateAdd:-3,'y' | Date:'yyyy' %} {% assign 2yearsback = 'Now' | DateAdd:-2,'y' | Date:'yyyy' %} {% assign 1yearback = 'Now' | DateAdd:-1,'y' | Date:'yyyy' %} {% assign currentyear = 'Now' | Date:'yyyy' %} {% assign 3yearpresent = 'False' %} {% assign rowcount = 0 %} {% assign CurrentTotal = 0 %} {% assign currentPercentTotal = 0 %} {% assign currentmonth = 'Now' | Date:'M' | AsInteger %} {% for row in rows %} {% if row.Year == 'Total' %} {% assign JanuaryTotal = row.January %} {% assign FebruaryTotal = row.February %} {% assign MarchTotal = row.March %} {% assign AprilTotal = row.April %} {% assign MayTotal = row.May %} {% assign JuneTotal = row.June %} {% assign JulyTotal = row.July %} {% assign AugustTotal = row.August %} {% assign SeptemberTotal = row.September %} {% assign OctoberTotal = row.October %} {% assign NovemberTotal = row.November %} {% assign DecemberTotal = row.December %} {% assign 3YearTotal = row.Total %} {% assign JanuaryPercent = row.January | DividedBy: row.Total,4 %} {% assign FebruaryPercent = row.February | DividedBy: row.Total,4 %} {% assign MarchPercent = row.March | DividedBy: row.Total,4 %} {% assign AprilPercent = row.April | DividedBy: row.Total,4 %} {% assign MayPercent = row.May | DividedBy: row.Total,4 %} {% assign JunePercent = row.June | DividedBy: row.Total,4 %} {% assign JulyPercent = row.July | DividedBy: row.Total,4 %} {% assign AugustPercent = row.August | DividedBy: row.Total,4 %} {% assign SeptemberPercent = row.September | DividedBy: row.Total,4 %} {% assign OctoberPercent = row.October | DividedBy: row.Total,4 %} {% assign NovemberPercent = row.November | DividedBy: row.Total,4 %} {% assign DecemberPercent = row.December | DividedBy: row.Total,4 %} {% endif %} {% if row.Year == currentyear %} {% assign JanuaryCurrent = row.January %} {% assign FebruaryCurrent = row.February %} {% assign MarchCurrent = row.March %} {% assign AprilCurrent = row.April %} {% assign MayCurrent = row.May %} {% assign JuneCurrent = row.June %} {% assign JulyCurrent = row.July %} {% assign AugustCurrent = row.August %} {% assign SeptemberCurrent = row.September %} {% assign OctoberCurrent = row.October %} {% assign NovemberCurrent = row.November %} {% assign DecemberCurrent = row.December %} {% assign TotalCurrent = row.Total %} {% endif %} {% if row.Year == 3yearsback %} {% assign 3yearpresent = 'True' %} {% endif %} {% endfor %} <div class="row" style="margin-bottom:50px;"> {% for row in rows %} {% if 3yearpresent == 'False' and rowcount == 0 %} {% assign rowcount = 1 %} <div class="col-md-3"> <table style="width:100%"> <thead> <tr> <th class="year" colspan="3"> {{3yearsback}}</th> </tr> <tr> <tr> <th class="text-right"> Month </th> <th class="text-right"> Total Given </th> <th class="text-right"> % of Total </th> </tr> </thead> <tbody> <tr> <td class="text-right"> Jan. {{3yearsback | Date:'yyyy'}} </td> <td class="text-right"> </td> <td class="text-right"> </td> </tr> <tr> <td class="text-right"> Feb. {{3yearsback | Date:'yyyy'}} </td> <td class="text-right"> </td> <td class="text-right"> </td> </tr> <tr> <td class="text-right"> Mar. {{3yearsback | Date:'yyyy'}} </td> <td class="text-right"> </td> <td class="text-right"> </td> </tr> <tr> <td class="text-right"> Apr. {{3yearsback | Date:'yyyy'}} </td> <td class="text-right"> </td> <td class="text-right"> </td> </tr> <tr> <td class="text-right"> May {{3yearsback | Date:'yyyy'}} </td> <td class="text-right"> </td> <td class="text-right"> </td> </tr> <tr> <td class="text-right"> Jun. {{3yearsback | Date:'yyyy'}} </td> <td class="text-right"> </td> <td class="text-right"> </td> </tr> <tr> <td class="text-right"> Jul. {{3yearsback | Date:'yyyy'}} </td> <td class="text-right"> </td> <td class="text-right"> </td> </tr> <tr> <td class="text-right"> Aug. {{3yearsback | Date:'yyyy'}} </td> <td class="text-right"> </td> <td class="text-right"> </td> </tr> <tr> <td class="text-right"> Sep. {{3yearsback | Date:'yyyy'}} </td> <td class="text-right"> </td> <td class="text-right"> </td> </tr> <tr> <td class="text-right"> Oct. {{3yearsback | Date:'yyyy'}} </td> <td class="text-right"> </td> <td class="text-right"> </td> </tr> <tr> <td class="text-right"> Nov. {{3yearsback | Date:'yyyy'}} </td> <td class="text-right"> </td> <td class="text-right"> </td> </tr> <tr> <td class="text-right"> Dec. {{3yearsback | Date:'yyyy'}} </td> <td class="text-right"> </td> <td class="text-right"> </td> </tr> <tfoot> <tr> <th class="text-right"> {{3yearsback | Date:'yyyy'}} Total:</th> <th class="text-right"> </th> <th class="text-right"> </th> </tr> </tfoot> </tbody> </table> </div> {% elseif row.Year == 3yearsback %} <div class="col-md-3"> <table style="width:100%"> <thead> <tr> <th class="year" colspan="3"> {{3yearsback}}</th> </tr> <tr> <tr> <th class="text-right"> Month </th> <th class="text-right"> Total Given </th> <th class="text-right"> % of Total </th> </tr> </thead> <tbody> <tr> <td class="text-right"> Jan. {{3yearsback | Date:'yyyy'}} </td> <td class="text-right"> {{row.January | FormatAsCurrency}} </td> <td class="text-right"> {{row.January | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Feb. {{3yearsback | Date:'yyyy'}} </td> <td class="text-right"> {{row.February | FormatAsCurrency}} </td> <td class="text-right"> {{row.February | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Mar. {{3yearsback | Date:'yyyy'}} </td> <td class="text-right"> {{row.March | FormatAsCurrency}} </td> <td class="text-right"> {{row.March | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Apr. {{3yearsback | Date:'yyyy'}} </td> <td class="text-right"> {{row.April | FormatAsCurrency}} </td> <td class="text-right"> {{row.April | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> May {{3yearsback | Date:'yyyy'}} </td> <td class="text-right"> {{row.May | FormatAsCurrency}} </td> <td class="text-right"> {{row.May | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Jun. {{3yearsback | Date:'yyyy'}} </td> <td class="text-right"> {{row.June | FormatAsCurrency}} </td> <td class="text-right"> {{row.June | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Jul. {{3yearsback | Date:'yyyy'}} </td> <td class="text-right"> {{row.July | FormatAsCurrency}} </td> <td class="text-right"> {{row.July | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Aug. {{3yearsback | Date:'yyyy'}} </td> <td class="text-right"> {{row.August | FormatAsCurrency}} </td> <td class="text-right"> {{row.August | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Sep. {{3yearsback | Date:'yyyy'}} </td> <td class="text-right"> {{row.September | FormatAsCurrency}} </td> <td class="text-right"> {{row.September | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Oct. {{3yearsback | Date:'yyyy'}} </td> <td class="text-right"> {{row.October | FormatAsCurrency}} </td> <td class="text-right"> {{row.October | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Nov. {{3yearsback | Date:'yyyy'}} </td> <td class="text-right"> {{row.November | FormatAsCurrency}} </td> <td class="text-right"> {{row.November | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Dec. {{3yearsback | Date:'yyyy'}} </td> <td class="text-right"> {{row.December | FormatAsCurrency}} </td> <td class="text-right"> {{row.December | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tfoot> <tr> <th class="text-right"> {{3yearsback | Date:'yyyy'}} Total:</th> <th class="text-right"> {{row.Total | FormatAsCurrency}}</th> <th class="text-right"> {{row.Total | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }}</th> </tr> </tfoot> </tbody> </table> </div> {% endif %} {% if row.Year == 2yearsback %} <div class="col-md-3"> <table style="width:100%"> <thead> <tr> <th class="year" colspan="3"> {{2yearsback}}</th> </tr> <tr> <tr> <th class="text-right"> Month </th> <th class="text-right"> Total Given </th> <th class="text-right"> % of Total </th> </tr> </thead> <tbody> <tr> <td class="text-right"> Jan. {{2yearsback | Date:'yyyy'}} </td> <td class="text-right"> {{row.January | FormatAsCurrency}} </td> <td class="text-right"> {{row.January | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Feb. {{2yearsback | Date:'yyyy'}} </td> <td class="text-right"> {{row.February | FormatAsCurrency}} </td> <td class="text-right"> {{row.February | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Mar. {{2yearsback | Date:'yyyy'}} </td> <td class="text-right"> {{row.March | FormatAsCurrency}} </td> <td class="text-right"> {{row.March | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Apr. {{2yearsback | Date:'yyyy'}} </td> <td class="text-right"> {{row.April | FormatAsCurrency}} </td> <td class="text-right"> {{row.April | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> May {{2yearsback | Date:'yyyy'}} </td> <td class="text-right"> {{row.May | FormatAsCurrency}} </td> <td class="text-right"> {{row.May | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Jun. {{2yearsback | Date:'yyyy'}} </td> <td class="text-right"> {{row.June | FormatAsCurrency}} </td> <td class="text-right"> {{row.June | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Jul. {{2yearsback | Date:'yyyy'}} </td> <td class="text-right"> {{row.July | FormatAsCurrency}} </td> <td class="text-right"> {{row.July | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Aug. {{2yearsback | Date:'yyyy'}} </td> <td class="text-right"> {{row.August | FormatAsCurrency}} </td> <td class="text-right"> {{row.August | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Sep. {{2yearsback | Date:'yyyy'}} </td> <td class="text-right"> {{row.September | FormatAsCurrency}} </td> <td class="text-right"> {{row.September | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Oct. {{2yearsback | Date:'yyyy'}} </td> <td class="text-right"> {{row.October | FormatAsCurrency}} </td> <td class="text-right"> {{row.October | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Nov. {{2yearsback | Date:'yyyy'}} </td> <td class="text-right"> {{row.November | FormatAsCurrency}} </td> <td class="text-right"> {{row.November | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Dec. {{2yearsback | Date:'yyyy'}} </td> <td class="text-right"> {{row.December | FormatAsCurrency}} </td> <td class="text-right"> {{row.December | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tfoot> <tr> <th class="text-right"> {{2yearsback | Date:'yyyy'}} Total:</th> <th class="text-right"> {{row.Total | FormatAsCurrency}}</th> <th class="text-right"> {{row.Total | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }}</th> </tr> </tfoot> </tbody> </table> </div> {% endif %} {% if row.Year == 1yearback %} <div class="col-md-3"> <table style="width:100%"> <thead> <tr> <th class="year" colspan="3"> {{1yearback}}</th> </tr> <tr> <tr> <th class="text-right"> Month </th> <th class="text-right"> Total Given </th> <th class="text-right"> % of Total </th> </tr> </thead> <tbody> <tr> <td class="text-right"> Jan. {{1yearback | Date:'yyyy'}} </td> <td class="text-right"> {{row.January | FormatAsCurrency}} </td> <td class="text-right"> {{row.January | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Feb. {{1yearback | Date:'yyyy'}} </td> <td class="text-right"> {{row.February | FormatAsCurrency}} </td> <td class="text-right"> {{row.February | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Mar. {{1yearback | Date:'yyyy'}} </td> <td class="text-right"> {{row.March | FormatAsCurrency}} </td> <td class="text-right"> {{row.March | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Apr. {{1yearback | Date:'yyyy'}} </td> <td class="text-right"> {{row.April | FormatAsCurrency}} </td> <td class="text-right"> {{row.April | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> May {{1yearback | Date:'yyyy'}} </td> <td class="text-right"> {{row.May | FormatAsCurrency}} </td> <td class="text-right"> {{row.May | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Jun. {{1yearback | Date:'yyyy'}} </td> <td class="text-right"> {{row.June | FormatAsCurrency}} </td> <td class="text-right"> {{row.June | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Jul. {{1yearback | Date:'yyyy'}} </td> <td class="text-right"> {{row.July | FormatAsCurrency}} </td> <td class="text-right"> {{row.July | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Aug. {{1yearback | Date:'yyyy'}} </td> <td class="text-right"> {{row.August | FormatAsCurrency}} </td> <td class="text-right"> {{row.August | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Sep. {{1yearback | Date:'yyyy'}} </td> <td class="text-right"> {{row.September | FormatAsCurrency}} </td> <td class="text-right"> {{row.September | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Oct. {{1yearback | Date:'yyyy'}} </td> <td class="text-right"> {{row.October | FormatAsCurrency}} </td> <td class="text-right"> {{row.October | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Nov. {{1yearback | Date:'yyyy'}} </td> <td class="text-right"> {{row.November | FormatAsCurrency}} </td> <td class="text-right"> {{row.November | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Dec. {{1yearback | Date:'yyyy'}} </td> <td class="text-right"> {{row.December | FormatAsCurrency}} </td> <td class="text-right"> {{row.December | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tfoot> <tr> <th class="text-right"> {{1yearback | Date:'yyyy'}} Total:</th> <th class="text-right"> {{row.Total | FormatAsCurrency}}</th> <th class="text-right"> {{row.Total | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }}</th> </tr> </tfoot> </tbody> </table> </div> {% endif %} {% if row.Year == 'Total' %} <div class="col-md-3"> <table style="width:100%"> <thead> <tr> <th class="year" colspan="3"> 3 Year Total</th> </tr> <tr> <tr> <th class="text-right"> Month </th> <th class="text-right"> Total Given </th> <th class="text-right"> % of Total </th> </tr> </thead> <tbody> <tr> <td class="text-right"> Jan. </td> <td class="text-right"> {{JanuaryTotal | FormatAsCurrency}} </td> <td class="text-right"> {{ JanuaryTotal | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Feb. </td> <td class="text-right"> {{FebruaryTotal | FormatAsCurrency}} </td> <td class="text-right"> {{ FebruaryTotal | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Mar. </td> <td class="text-right"> {{MarchTotal | FormatAsCurrency}} </td> <td class="text-right"> {{ MarchTotal | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Apr. </td> <td class="text-right"> {{AprilTotal | FormatAsCurrency}} </td> <td class="text-right"> {{ AprilTotal | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> May </td> <td class="text-right"> {{MayTotal | FormatAsCurrency}} </td> <td class="text-right"> {{ MayTotal | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Jun. </td> <td class="text-right"> {{JuneTotal | FormatAsCurrency}} </td> <td class="text-right"> {{ JuneTotal | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Jul. </td> <td class="text-right"> {{JulyTotal | FormatAsCurrency}} </td> <td class="text-right"> {{ JulyTotal | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Aug. </td> <td class="text-right"> {{AugustTotal | FormatAsCurrency}} </td> <td class="text-right"> {{ AugustTotal | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Sep. </td> <td class="text-right"> {{SeptemberTotal | FormatAsCurrency}} </td> <td class="text-right"> {{ SeptemberTotal | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Oct. </td> <td class="text-right"> {{OctoberTotal | FormatAsCurrency}} </td> <td class="text-right"> {{ OctoberTotal | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Nov. </td> <td class="text-right"> {{NovemberTotal | FormatAsCurrency}} </td> <td class="text-right"> {{ NovemberTotal | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tr> <td class="text-right"> Dec. </td> <td class="text-right"> {{DecemberTotal | FormatAsCurrency}} </td> <td class="text-right"> {{ DecemberTotal | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }} </td> </tr> <tfoot> <tr> <th class="text-right"> Total</th> <th class="text-right"> {{row.Total | FormatAsCurrency}}</th> <th class="text-right"> {{row.Total | DividedBy:row.Total,4 | Times:100 | Format:'#,##0.00' | Append:' %' }}</th> </tr> </tfoot> </tbody> </table> </div> {% endif %} {% endfor %} </div> <div class="row"> <div class="col-md-5"> <table style="width:100%"> <thead> <tr> <th class="year" colspan="4"> {{currentyear}} Projections</th> </tr> <tr> <tr> <th class="text-right"> Month </th> <th class="text-right"> Total Given </th> <th class="text-right"> % Multiplier </th> <th class="text-right"> Year End Projection </th> </tr> </thead> <tbody> <tr> <td class="text-right"> Jan. {{currentyear}} </td> <td class="text-right"> {{JanuaryCurrent | FormatAsCurrency}} </td> {% if currentmonth >= 1 %} <td class="text-right"> {{JanuaryPercent | Times:100 | Format:'#,##0.00' | Append:' %'}} </td> <td class="text-right"> {{JanuaryCurrent | DividedBy:JanuaryPercent | FormatAsCurrency}} </td> {% endif %} {% if currentmonth > 1%} {% assign currentPercentTotal = currentPercentTotal | Plus:JanuaryPercent %} {% assign CurrentTotal = CurrentTotal | Plus:JanuaryCurrent %} {% endif %} </tr> <tr> <td class="text-right"> Feb. {{currentyear}} </td> <td class="text-right"> {{FebruaryCurrent | FormatAsCurrency}} </td> {% if currentmonth >= 2 %} <td class="text-right"> {{FebruaryPercent | Times:100 | Format:'#,##0.00' | Append:' %'}} </td> <td class="text-right"> {{FebruaryCurrent | DividedBy:FebruaryPercent | FormatAsCurrency}} </td> {% endif %} {% if currentmonth > 2 %} {% assign currentPercentTotal = currentPercentTotal | Plus:FebruaryPercent %} {% assign CurrentTotal = CurrentTotal | Plus:FebruaryCurrent %} {% endif %} </tr> <tr> <td class="text-right"> Mar. {{currentyear}} </td> <td class="text-right"> {{MarchCurrent | FormatAsCurrency}} </td> {% if currentmonth >= 3 %} <td class="text-right"> {{MarchPercent | Times:100 | Format:'#,##0.00' | Append:' %'}} </td> <td class="text-right"> {{MarchCurrent | DividedBy:MarchPercent | FormatAsCurrency}} </td> {% endif %} {% if currentmonth > 3 %} {% assign currentPercentTotal = currentPercentTotal | Plus:MarchPercent %} {% assign CurrentTotal = CurrentTotal | Plus:MarchCurrent %} {% endif %} </tr> <tr> <td class="text-right"> Apr. {{currentyear}} </td> <td class="text-right"> {{AprilCurrent | FormatAsCurrency}} </td> {% if currentmonth >= 4 %} <td class="text-right"> {{AprilPercent | Times:100 | Format:'#,##0.00' | Append:' %'}} </td> <td class="text-right"> {{AprilCurrent | DividedBy:AprilPercent | FormatAsCurrency}} </td> {% endif %} {% if currentmonth > 4 %} {% assign currentPercentTotal = currentPercentTotal | Plus:AprilPercent %} {% assign CurrentTotal = CurrentTotal | Plus:AprilCurrent %} {% endif %} </tr> <tr> <td class="text-right"> May {{currentyear}} </td> <td class="text-right"> {{MayCurrent | FormatAsCurrency}} </td> {% if currentmonth >= 5 %} <td class="text-right"> {{MayPercent | Times:100 | Format:'#,##0.00' | Append:' %'}} </td> <td class="text-right"> {{MayCurrent | DividedBy:MayPercent | FormatAsCurrency}} </td> {% endif %} {% if currentmonth > 5 %} {% assign currentPercentTotal = currentPercentTotal | Plus:MayPercent %} {% assign CurrentTotal = CurrentTotal | Plus:MayCurrent %} {% endif %} </tr> <tr> <td class="text-right"> Jun. {{currentyear}} </td> <td class="text-right"> {{JuneCurrent | FormatAsCurrency}} </td> {% if currentmonth >= 6 %} <td class="text-right"> {{JunePercent | Times:100 | Format:'#,##0.00' | Append:' %'}} </td> <td class="text-right"> {{JuneCurrent | DividedBy:JunePercent | FormatAsCurrency}} </td> {% endif %} {% if currentmonth > 6 %} {% assign currentPercentTotal = currentPercentTotal | Plus:JunePercent %} {% assign CurrentTotal = CurrentTotal | Plus:JuneCurrent %} {% endif %} </tr> <tr> <td class="text-right"> Jul. {{currentyear}} </td> <td class="text-right"> {{JulyCurrent | FormatAsCurrency}} </td> {% if currentmonth >= 7 %} <td class="text-right"> {{JulyPercent | Times:100 | Format:'#,##0.00' | Append:' %'}} </td> <td class="text-right"> {{JulyCurrent | DividedBy:JulyPercent | FormatAsCurrency}} </td> {% endif %} {% if currentmonth > 7 %} {% assign currentPercentTotal = currentPercentTotal | Plus:JulyPercent %} {% assign CurrentTotal = CurrentTotal | Plus:JulyCurrent %} {% endif %} </tr> <tr> <td class="text-right"> Aug. {{currentyear}} </td> <td class="text-right"> {{AugustCurrent | FormatAsCurrency}} </td> {% if currentmonth >= 8 %} <td class="text-right"> {{AugustPercent | Times:100 | Format:'#,##0.00' | Append:' %'}} </td> <td class="text-right"> {{AugustCurrent | DividedBy:AugustPercent | FormatAsCurrency}} </td> {% endif %} {% if currentmonth > 8 %} {% assign currentPercentTotal = currentPercentTotal | Plus:AugustPercent %} {% assign CurrentTotal = CurrentTotal | Plus:AugustCurrent %} {% endif %} </tr> <tr> <td class="text-right"> Sep. {{currentyear}} </td> <td class="text-right"> {{SeptemberCurrent | FormatAsCurrency}} </td> {% if currentmonth >= 9 %} <td class="text-right"> {{SeptemberPercent | Times:100 | Format:'#,##0.00' | Append:' %'}} </td> <td class="text-right"> {{SeptemberCurrent | DividedBy:SeptemberPercent | FormatAsCurrency}} </td> {% endif %} {% if currentmonth > 9 %} {% assign currentPercentTotal = currentPercentTotal | Plus:SeptemberPercent %} {% assign CurrentTotal = CurrentTotal | Plus:SeptemberCurrent %} {% endif %} </tr> <tr> <td class="text-right"> Oct. {{currentyear}} </td> <td class="text-right"> {{OctoberCurrent | FormatAsCurrency}} </td> {% if currentmonth >= 10 %} <td class="text-right"> {{OctoberPercent | Times:100 | Format:'#,##0.00' | Append:' %'}} </td> <td class="text-right"> {{OctoberCurrent | DividedBy:OctoberPercent | FormatAsCurrency}} </td> {% endif %} {% if currentmonth > 10 %} {% assign currentPercentTotal = currentPercentTotal | Plus:OctoberPercent %} {% assign CurrentTotal = CurrentTotal | Plus:OctoberCurrent %} {% endif %} </tr> <tr> <td class="text-right"> Nov. {{currentyear}} </td> <td class="text-right"> {{NovemberCurrent | FormatAsCurrency}} </td> {% if currentmonth >= 11 %} <td class="text-right"> {{NovemberPercent | Times:100 | Format:'#,##0.00' | Append:' %'}} </td> <td class="text-right"> {{NovemberCurrent | DividedBy:NovemberPercent | FormatAsCurrency}} </td> {% endif %} {% if currentmonth > 11 %} {% assign currentPercentTotal = currentPercentTotal | Plus:NovemberPercent %} {% assign CurrentTotal = CurrentTotal | Plus:NovemberCurrent %} {% endif %} </tr> <tr> <td class="text-right"> Dec. {{currentyear}} </td> <td class="text-right"> {{DecemberCurrent | FormatAsCurrency}} </td> {% if currentmonth == 12 %} <td class="text-right"> {{DecemberPercent | Times:100 | Format:'#,##0.00' | Append:' %'}} </td> <td class="text-right"> {{DecemberCurrent | DividedBy:DecemberPercent | FormatAsCurrency}} </td> {% assign currentPercentTotal = currentPercentTotal | Plus:DecemberPercent %} {% assign CurrentTotal = CurrentTotal | Plus:DecemberCurrent %} {% endif %} </tr> </tbody> </table> </div> <div class="col-md-7 row" style="margin-top:50px;"> <div class="col-md-10 col-md-offset-2"> <table style="width:100%"> <tr> <th> Year</th> <th> Total</th> </tr> {% for row in rows %} {% if row.Year != 'Total' %} <tr> <td> {{row.Year}}</td> <td> {{row.Total | FormatAsCurrency }}</td> </tr> {% endif %} {% endfor %} </table> </div> <div class="col-md-6 col-md-offset-2 alert-success alert" style="margin-top:50px;"> <h4> {{Currentyear}} Year End Projections:</h4> {% if currentmonth > 1 %}<p> {{ CurrentTotal | DividedBy: currentPercentTotal | FormatAsCurrency}}</p> {% else %}<p> One Month of the Year Must Be Completed</p> {% endif%} </div> </div> </div> <div class="chart-container" style="position: relative; height:300px; width:100%; margin-bottom:50px;"> <h4> Giving History By Month</h4> <canvas id="myChart"> </canvas> </div> <script> var ctx = document.getElementById("myChart").getContext('2d'); var myChart = new Chart(ctx, { type: 'line', data: { labels: ["January", "February", "March", "April", "May", "June", "July", "August", "September", "October","November","December"], datasets: [ {% for row in rows %} {% if row.Year != 'Total'%} { label: '{{row.Year}}', fill: false, data: [{{row.January}},{{row.February}},{{row.March}},{{row.April}},{{row.May}},{{row.June}},{{row.July}},{{row.August}},{{row.September}},{{row.October}},{{row.November}},{{row.December}}], {% if row.Year == 3yearsback %} {% assign color = 'rgba(54,162,235,' %} {% elseif row.Year == 2yearsback %} {% assign color = 'rgba(75,192,192,' %} {% elseif row.year == 1yearback %} {% assign color = 'rgba(255,159,64,' %} {% elseif row.year == currentyear %} {% assign color = 'rgba(153,102,255,' %} {% else %} {% assign color = 'rgba(153, 102, 255,' %} {% endif %} backgroundColor: [ '{{color}} 1)', ], borderColor: [ '{{color}} 1)', ], borderWidth: 1 }, {% endif %} {% endfor %} ] }, options: { responsive: true, maintainAspectRatio: false, animation: { duration: 2500, }, legend: { position: 'right' }, scales: { yAxes: [{ ticks: { beginAtZero:false } }] } } }); </script>
Rob Howell Reply 6 months ago I am trying to figure out the best way to enter historical financial data to fill in the gap that is missing. Any suggestions on the best way to do this?
Matt Whitsell Reply one year ago Would there be a way to add a current year "budget goal" to the "Year End Projections" box? This would allow the user to see if they were on track to making budget at a glance.
Guy House Reply one year ago When I copy and paste in my sql query I get Error SQL Error! Incorrect syntax near 'Now'. Incorrect syntax near 'Now'. Why is this? If change this then it says 'yyyy' is incorrect then 'as' is incorrect, which i can't find anywhere. thoughts??
Kye "K" Chung one year ago I just created separate campus pages with different financial accounts for each campus.
Liz Rhoades 8 months ago Did you ever get this tweaked for a fiscal year run? I am digging in now to see if I can get it set that way as well, but thought I'd see if you had set it up that way.
Kevin Rutledge Reply one year ago I fixed an issue in the sql so you may want to copy and past again. I was gathering data 3 years from the current date which would throw off the totals as we progressed in the year. K Chung, I believe it is line 58 and TransactionDateTime < '{{'Now' | Date:'MM/01/yyyy'}}' . This is the line that looks before the current month. If you remove it, it will give everything to the current date. In the output, you will want to remove this if statement and the else clause. {% if currentmonth != 1 %}
Andrew Torrance Reply one year ago Great Report! - Any idea why the first row shows 2017 at the top instead of 2016?
Kye "K" Chung Reply one year ago Where in the code do you "change the start date in one place if you don't want these limitations"?