Help with grouping and summation

[skiingdave]

I'm having a hard time with something that I think should be pretty simple. I have a collection, MergedScores, that looks like this:

ParticipantEmail	ParticipantName	TotalQuestionPoints	TotalEventPoints
a@null.net	A A	5
b@null.net	B B	5
a@null.net	A A		25

I'm trying to group the collection by email, sum up their question and event points into a new TotalPoints column so the above would become

ParticipantEmail	ParticipantName	TotalPoints
a@null.net	A A	30
b@null.net	B B	5

This is what I think should work, but I just get errors that TotalEventPoints and TotalQuestionPoints aren't recognized.

ClearCollect(GroupedScores, 
  AddColumns(
    GroupBy(MergedScores, ParticipantEmail, ParticipantName, GroupedQandE) As myGrouping, 
    TotalPoints, 
    Sum(myGrouping.GroupedQandE, TotalQuestionPoints, TotalEventPoints)
    )
);

What am I doing wrong? Thanks.

[LaurensM]

Hi @skiingdave, 😊

When using the sum function to sum a table of numbers, it expects 2 parameters. More than 2 parameters is used to calculate the sum of the individual parameters.

Instead of using a comma between TotalQuestionPoints and TotalEvenPoints, use the addition operator:

Sum(myGrouping.GroupedQandE, TotalQuestionPoints + TotalEventPoints)

---

If this solves your question, would you be so kind as to accept it as a solution. ✔️

If you liked my solution, please give it a thumbs up. 👍

Connect with me: LinkedIn | Blog

[skiingdave]

Thanks, this just gives me a generic "there is an error with the formula. Please make corrections and try again" without underlining anything in red so I'm not sure what the issue is. In any case, I've got the app working - I just had to split each step into a separate line of code, which seems kinda clunky but it's fine.

[skiingdave]

This is what I wound up doing to get it to work. I had to do it as two separate lines :

// group the two scores tables by user
ClearCollect(GroupedScores, 
       AddColumns(
        GroupBy(MergedScores, ParticipantEmail,  ParticipantName, GroupedQandE) As GroupedTemp, 
        TotalEventPoints, 
        Sum(GroupedTemp.GroupedQandE, TotalEventPoints), 
        TotalQuestionPoints, 
        Sum(GroupedTemp.GroupedQandE, TotalQuestionPoints)      
));

// Add a Total Score column that is the sum of question and event Points. Clunky but you have to do the above steps and this one at a time.
// Also get rid of the grouped subtable column while we're at it, though I don't think it matters much
ClearCollect(TotalScores, 
        DropColumns(
            AddColumns(GroupedScores, TotalPoints, TotalEventPoints + TotalQuestionPoints), 
            GroupedQandE
        )
);

[LaurensM]

Thanks for the additional info @skiingdave. Normally the provided code should do the trick, I have written it out in full below. As a first troubleshooting step, save & reopen the Canvas Editor to see whether it affects the error.

ClearCollect(
    GroupedScores, 
    AddColumns(
        GroupBy(MergedScores, ParticipantEmail, ParticipantName, GroupedQandE) As myGrouping, 
        TotalPoints, 
        Sum(myGrouping.GroupedQandE, TotalQuestionPoints + TotalEventPoints)
    )
);

The code above resulted in the following collection:

You could remove the nested table column via a DropColumns function.

Your current workaround will work, but it could be worth retrying the approach above in order to improve performance & reduce the amount of code.

I hope this helps!