Oct 05, 2017 12:00 PM
Hello,
Im building a database with all tasks that we do.
Id like to answear these kind of question:
Whats the average number of tasks that we complete per week?
How can I achieve this?
Tasks Name, Done Date and Week Number:
Oct 05, 2017 03:20 PM
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.
Oct 05, 2017 03:52 PM
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?
Oct 15, 2017 11:08 AM
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:
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…