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

3

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

image
image3024×1400 302 KB

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)
   
   
   )))

image
image3001×1542 336 KB

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

Cheers, Christiaan

1 Like