Average grouped by a column

You can achieve this kind of thing with formula fields and grouped views. For example, use a formula to calculate Year and Month from a date:

You can then group by this calculated field…

In this case of Time tracked against projects - and you can see totals by month.

Yeap.
But how can I count how many tasks I have per group then use it to say: In average, I deliver 3 tasks per week?

If you look at my Sales CRM Dashboard base in Airtable Universe, you’ll see the process I followed to generate (er, fake) bar charts for ‘Contacts by Month’ is essentially the same as what you’ll need to determine tasks per week. Unfortunately, to achieve the effect I desired, I had to tailor a formula to extract each month’s figures from the rollup. With only a dozen months, the task wasn’t too painful – but I would really hate to have to hand-build 52 columns…

One possibility might be to analyze the data vertically rather than horizontally. Yeah, that’s it:

• Create a table called, oh, ‘WeekCalc’. It will contain only a single row.

• In such cases, I usually set the primary field of that single row to something like a check-mark emoji. (See the ‘DashboardCalc’ table in my CRM Dashboard for an example.)

• Add a column to the table from your first post. Make it a link to the ‘WeekCalc’ table. Every record from that table must link to the single record in ‘WeekCalc’.
  Easiest way to do that:

  • Mark and copy the primary field value from the ‘WeekCalc’ record.
  • Add a column to the table from your first post and define it as a single-line text field.
  • Select the first cell of that column, scroll to the bottom of the table, and, while holding down the ‘Shift’ key, select the bottom-most cell. This will result in every cell in the column being selected – and if you look quickly to the lower left of your screen, you should see a message to the effect of ‘#### cells selected.’
  • Press ‘Ctrl-V’. This will paste the value copied from ‘WeekCalc’ into every cell of the new column.
  • Right-click on the field, select ‘Customize field type,’ and change the field type to ‘Link to another record’ with ‘WeekCalc’ as the table.
  • Airtable grumbles for few seconds, the cell contents turn a pale shade of blue — and you now have every row linked to the single record in ‘WeekCalc’.

• Create a new field in ‘WeekCalc’. Define it as a rollup of the {Weekday} field in, um, whatever that table is called, with an aggregation function of ARRAYJOIN(values,'|')&'|'. (Yes, append “&'|'” to the ARRAYJOIN() function.)

• Create yet another new table and call it ‘Weeks’.

• Inside ‘Weeks’, create 52 (53?) records. Set the primary field to ‘01’ through ‘52’ (‘53’?) sequentially.

• Repeat that entire subroutine from 4 bullets above to link every ‘Weeks’ record to the single ‘WeekCalc’ record.

• Create a new column in ‘Weeks’, defined as a lookup of that rollup field you just added to ‘WeekCalc’. Call it ‘LookedUpRollup’.

• Finally, create another new column in ‘Weeks’. Call it ‘TasksPerWeek’ and define it as a formula with this configuration:

  (LEN(LookedUpRollup) -
LEN(SUBSTITUTE(LookedUpRollup,WeeksPrimary&'|',''))/3

  That will calculate how many tasks were completed during each week of the year.

At this point, you can find your average per-week load, your high- and low-water marks, and so forth.

This assumes WEEKNUM() for the first 9 weeks of the year returns a two-digit value with a leading ‘0’ — which I just discovered it doesn’t. So you’ll either have to modify your initial table to convert week numbers to strings and left-pad with ‘0’ as needed or to modify the formula above to divide the difference between the two LEN()s by 2 if {WeeksPrimary}<10, whichever seems a more-logical approach to you.

Again, this is essentially nothing more than a reworking of a few fields from the ‘Dashboard’ and ‘DashboardCalc’ tables from my Sales CRM Dashboard base, so if my stream-of-consciousness narrative, above, is less than crystalline, take a look at that base to figure out what I meant to say…