Adding running sum in group by date in new row

Question

I'm working Syncfusion DocIO with creating tables in .docx with some props and can't create row with group by date => on every new year I need new row with running sum. I already create sum row, but this row returns all sum for additional props (columns), so all I need is for each year in new row running sum.

My example:

Months	Anuity	InterestPaid	PrincipalPaid	BalanceDue	Date
1	84,69	2,50	82,19	917,81	2021/11/12
2	84,69	2,29	82,40	835,41	2021/12/12
Sum for year 2021
3	84,69	2,09	82,61	752,80	2022/1/12
4	84,69	1,88	82,81	669,99	2022/2/12
5	84,69	1,67	83,02	586,97	2022/3/12
6	84,69	1,47	83,23	503,75	2022/4/12
7	84,69	1,26	83,43	420,31	2022/5/12
8	84,69	1,05	83,64	336,67	2022/6/12
9	84,69	0,84	83,85	252,82	2022/7/12
10	84,69	0,63	84,06	168,75	2022/8/12
11	84,69	0,42	84,27	84,48	2022/9/12
12	84,69	0,21	84,48	0,00	2022/10/12
Sum for year 2022
Sum all	1016,32	16,32	1000,00

My code:

        int i = 1;
        
        foreach (var amortizationData in aAmortizationPlanLoanPayment)
        {
            WTableRow tableRow = tableAmortizationData.AddRow(true);

            tableRow.Cells[0].AddParagraph().AppendText(i.ToString());
            tableRow.Cells[1].AddParagraph().AppendText(amortizationData.Anuity.ToString());
            tableRow.Cells[2].AddParagraph().AppendText(amortizationData.InterestPaid.ToString());
            tableRow.Cells[3].AddParagraph().AppendText(amortizationData.PrincipalPaid.ToString());
            tableRow.Cells[4].AddParagraph().AppendText(amortizationData.BalanceDue.ToString());
            tableRow.Cells[5].AddParagraph().AppendText(amortizationData.Date.AddMonths(aAmortizationPlanLoanPaymentData.Moratorium).ToString("yyyy/M/dd"));
            i++;
            
            if (amortizationData.Date.Month == 12)
            {
                    WTableRow YearSumrow = tableAmortizationData.AddRow(true);

                    WTableCell cell1 = YearSumrow.Cells[0];

                    var sumYearIWTextRange = cell1.AddParagraph().AppendText($"Sum for year {amortizationData.Date.Year}");

                sumYearIWTextRange = YearSumrow.Cells[1].AddParagraph().AppendText(); // need here running sum inside appendText();

                sumYearIWTextRange = YearSumrow.Cells[2].AddParagraph().AppendText(); // need here running sum inside appendText();

                sumYearIWTextRange = YearSumrow.Cells[3].AddParagraph().AppendText(); // need here running sum inside appendText();
            }

            if (amortizationData.BalanceDue == 0.00)
            {
                WTableRow LastYearRow = tableAmortizationData.AddRow(true);

                WTableCell cellLastYear = LastYearRow.Cells[0];

                var sumLastYearIWTextRange = cellLastYear.AddParagraph().AppendText($"Sum for year {amortizationData.Date.Year}");

                sumLastYearIWTextRange = LastYearRow.Cells[1].AddParagraph().AppendText(); // need here running sum inside appendText();

                sumLastYearIWTextRange = LastYearRow.Cells[2].AddParagraph().AppendText(); // need here running sum inside appendText();

                sumLastYearIWTextRange = LastYearRow.Cells[3].AddParagraph().AppendText(); // need here running sum inside appendText();
            }
        }

        
        WTableRow tableRowSum = tableAmortizationData.AddRow(true);
        WTableCell cell = tableRowSum.Cells[0];

        var totalIWTextRage = cell.AddParagraph().AppendText("Sum all");

        var totalAmountIWTextRage = tableRowSum.Cells[1].AddParagraph().AppendText(aAmortizationPlanLoanPayment.Sum(p => p.Anuity).ToString());

        totalAmountIWTextRage = tableRowSum.Cells[2].AddParagraph().AppendText(aAmortizationPlanLoanPayment.Sum(p => p.InterestPaid).ToString());

        totalAmountIWTextRage = tableRowSum.Cells[3].AddParagraph().AppendText(aAmortizationPlanLoanPayment.Sum(p => p.PrincipalPaid).ToString());

"I already create sum row with Linq, but ...." Where is the LINQ ? Why can you not create this sum using Linq ? What did you try ? No one can tell why it failed, because you do not share what you tried.... — Luuk, Oct 05 '21 at 06:00
Taking out the irrelevant font size and bold lines would make that code a lot more readable for us; perhaps you should move those codes to a generator method to clean it up for real too — Caius Jard, Oct 05 '21 at 06:05
All in, it looks like the sort of thing I wouldn't use LINQ for; use a foreach loop instead. Linq is a hammer, but not every problem is a nail — Caius Jard, Oct 05 '21 at 06:06

Caius Jard · Answer 1 · 2021-10-05T06:30:56.300

0

If you had a bunch of numbers (already grouped and summed to distinct dates, but doesn't have to be);

(DateTime D, int V)[] rows = new [] {
  (new(1999, 12, 1), 111), 
  (new(2000, 1, 1), 222), 
  (new(2000, 2, 1), 333), 
  (new(2000, 3, 1), 444),
  (new(2001, 1, 1), 555)
}

Then sure, you could do your running sum with LINQ:

foreach(var row in rows){
  var runSum = rows.Where(r => r.D <= row.D).Sum(r => r.V);
}

But it's a lot more resource wasteful than just keeping a variable:

var runSum = 0;
foreach(var row in rows){
  runSum += row.V;
}

If you want your running sum to start over from time to time just remember the previous row (naturally your rows need to be in order):

var prevRow = rows[0];
var runSum = 0;
foreach(var row in rows){

  if(prevRow.D.Year != row.D.Year)
    runSum = 0;

  runSum += row.V;
}

Do your output above the line where you increment the runSum if you don't want the current line value to appear in the total, or below it if you do

edited Oct 05 '21 at 06:30

answered Oct 05 '21 at 06:21

Caius Jard

72,509
5
49
80

_"But it's a lot more resource wasteful than just keeping a variable"_ are we going to have that discussion? Your statement is quite opinionated. Many argue that the LINQ expression better states intent, and less error-prone (e.g. the for loop is missing the filter -> select/if statement). The potential loss in performance is often not critical, while the maintainability is improved. – JHBonarius Oct 05 '21 at 06:42
*Your statement is quite opinionated* - no; it's a fact. Having a list of numbers one thousand long, and incrementing a variable **one thousand times** is less resource intensive than incrementing a variable **five hundred thousand** times – Caius Jard Oct 05 '21 at 11:49
I think we're on a different wavelength. I thought you were talking about the general case of LINQ being less performant than a for-loop. Apparently you are not, or are you? I find it hard to compare your code examples, as each one seems to do something different. – JHBonarius Oct 05 '21 at 12:04
Oh, yes some confusion has possibly arisen; when I was observing a resource burn it was comparing something like the first code block of doing a LINQ sum of all previous values on every loop iteration, which is O((n^2)/2), with the simple increment (second code block) of an outside-loop var which would be O(n). – Caius Jard Oct 05 '21 at 14:09

Adding running sum in group by date in new row

1 Answers1