Help: how to combine and sum up attendance from multiple tables

Hello amazing community,

I need your kind help.

Been trying to find a way to do this for long hours but still cannot find a way.

I have three tables (cannot share the actual table due to privacy reasons):

Event Name
Date | Attendance
Jan 3 | 5
Jan 5 | 7
and so on

Event Name
Date | Attendance
Jan 6 | 51
Jan 3 | 7
and so on

Event Name
Date | Attendance
Jan 7 | 14
Jan 2 | 2
and so on

And here is what I’m looking to get

Weekly Attendance Across all events
Week | Total Attendance
Jan Week 1 (Jan 2 - Jan 8) | 110
Jan Week 2 (Jan 9- Jan 15) | 78

So basically i want to create a fourth table that will take the total attendance per week of all the events in the existing three tables. Example: table 1 has 2 attendance on jan 2 and 2 attendance on jan 4, table 2 has 3 attendance on jan 5 and table 3 has 1 attendance on jan 6 and 3 attendance on jan 8. Table 4 should combine all these attendance for january week 1 and input it on one row only which would be 2+2+3+1+3= 11.

Any kind soul can help me out?

Hi @Max_Wong
So I recreated your table like this, with the same structure

Then just create a table with week numbers directly from 1 to 52 (or date with +7 days difference to extract weeknumber)
image

Then, you just have to make the sum of Att., for your three tables, according to weeknumber of the date

[Table Event 1]
  .Filter(
    WeekNumber(Date) = thisRow.WeekNumber
  )
  .[Att.]
  .Sum()

Is that clear ?
Cheers

Quentin

hi @Max_Wong

I just noticed the contribution of @Quentin_Morel , mine is a bit different. I created the weeks via

thisTable.Find(thisRow).LeftPad(2,0) to cover all weeks and not just weeks that appear in your date range.

and the year (you need as well) via value for new rows.

We create the tables as you described

the code is:

[Table 01].Filter(theDate.WeekNumber().Contains(thisRow.theWeek) and theDate.Year().Contains(thisRow.theYear)).WithName(tb01,
  
 [Table 02].Filter(theDate.WeekNumber().Contains(thisRow.theWeek) and theDate .Year().Contains(thisRow.theYear)).WithName(tb02,
   
   [Table 03].Filter(theDate.WeekNumber().Contains(thisRow.theWeek) and theDate.Year().Contains(thisRow.theYear)).WithName(tb03, 
   
   Sum(tb01.Attendance,tb02.Attendance,tb03.Attendance)
   
   
   )))

You notice the use of WithName() to keep the code comprehensible.

Cheers, Christiaan

1 Like

Hi Max,

Coda would not be Coda if there were not different ways to do something.

I would very strongly recommended that you consider combining your various events tables into a single event table, with a column to identify the event .

Then your result is simply a sum of rows in the table. It is a much simpler, much more flexible structure.

It also reduces the maintenance you will need to do, if you decide to add a feature/ column to your event information.

Regards
Piet.

4 Likes

Best answer.

Always try to add a column (> a parameter, a tag, a category) to use Filter() instead of creating new table, especially when this is so close.

The system of view and filter do the rest…

1 Like

Just adding a small note regarding the use of WeekNumber() here :innocent: .

As in your 1st post you mentioned the 1st week of the year is

You might want to consider using IsoWeekNumber() which follows the ISO Standard: Weeks going from Monday to Sunday and where “week 1 contains the first Thursday of the year” per the formula documentation.
(AFAIK, week 1 is also the week containing the 4th of January… At least, this is the rule I follow in such cases :blush: )

IsoWeekNumber() is also not tied to day you selected as First day of the week (in Doc settingsRegion)

If you use WeekNumber() here, you would get Week 2 (Jan 2 - Jan 8) :blush: (of 2023 with the First day of the week set on Monday).

When using WeekNumber() the 1st week of the year is the week containing the 1st of January.
It is also doc dependant as it relies on the selected First day of the week.

This nuance between IsoWeekNumber() and WeekNumber() might or might not matter though (depending on your use case) :blush:

2 Likes

What fascinates me about dates/times, is how unbelievably complicated it gets. Just in the western world, there is the whole BC/ BCE concept, the Julian calendar adjustment, and it’s gradual implementation.

Then you have first day of the week, and first week of the year

Then add the Japanese emperor concept, Chinese dates, Easter and other religious celebrations…

Enough for a book!

Fortunately ISO 8601 sorts it all out for us.

P
Oh, AND daylight savings times, and India with its 30 minute offset…

1 Like

Oh wow thank you so much everyone

this community is awesome!

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.