List of options, summarized in a different table by month

1

I have a table of months.
I have a table of internship duties, each one chooses which month they will be completed in (some are just one month, some will be done over the course of many months). So options for an internship duty might look like the following:

  • [duty column] Answer emails [month column (lookup from table)] March, April, May [# of hours] 5

  • [duty column] Event 1 [month column (lookup from table)] March [# of hours] 10

What I want to do is in a separate table (separated by role), summarize ALL of March’s hours. So in this example the table would pull
March: Answer emails - 5 hours, and Event 1 10 hours = 15 total hours in March.

What it’s doing is returning each COMBINATION of months as rows in the table. So it would so show

  • March
  • March, April, May
    As my options instead of counting ‘answer emails’ in each of march, April, and May’s rows.

Here’s a video explanation if it helps: Screen Recording 2021-02-14...

2

Hi @Ruby_George ,

have a look at this and tell me if this is the desired outcome:

  1. Yes, you are right: the group by considers the unicity if the column, so you can’t actually have it in the same table
  2. The formula for Total Hours is pretty straightforward:
Duties.Filter(thisRow.In(Month)).[Month Hours].Sum()
// [From the end]: 
// Please, give me the sum of all the hours of Duties table where the month of thisRow appears among the months

Let me know if this helps.
Cheers!

1 Like
3

the In() function we not often come accros.
just check if the month is ‘In’ the list of months and if so, take the sum.
why don’t we see this function more often used @Federico_Stefanato ?

4

Hi @Christiaan_Huizer

Hmm… good question.
Honestly, I don’t know :smiley:

I think one of the reasons is that In() is somehow (*) symmetric to Contains() and can be perceived as redundant.

As a matter of fact, you can write also:

Duties.Filter([Month].Contains(thisRow)).[Month Hours].Sum()

(*)
“Somehow”, because In() accepts a single value search while Contains() searches for sublist within a list, therefore the former should be more performant (it really depends on the underlying implementation: don’t take this as a statement).

3 Likes
5

This is very helpful, thank you @frederico_stefananto - but I think I figured out what would be MORE helpful if it’s possible. Is there a way to “group” by month in this case? Where the same row would show up in multiple groups because it includes multiple groups as options?

video explaining what I mean: Screen Recording 2021-02-14...

6

this is helpful insight @Federico_Stefanato

it seems to imply that once you have a list filled with single items (seperated by a comma) the In() is maybe the way to go, while having a sublist [[sublist1],[sublist2],[sublist3]] Contains() looks like the right candidate.

I would love to understand better when to apply a function.

(*)
“Somehow”, because In() accepts a single value search while Contains() searches for sublist within a list, therefore the former should be more performant (it really depends on the underlying implementation: don’t take this as a statement).

7

Hi @Ruby_George ,
I see your point.

As said, you cannot group by the table itself, however you can take advantage of the reference Months table to aggregate the duties by month (via a bi-directional lookup).

Have a look at the modified example in the same doc and tell me if this work for you.

Basically, you just edit the Duties table and Months is directly affected.

Tell me is this is going to the right direction.
Cheers!

8

Hi @Christiaan_Huizer ,
I tried to sum up the differences here:

Let me know if you think there still something missing.

2 Likes