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…
