Here’s the process I came up with. I’m only going to describe how to track a single habit month-by-month. Just replicate the same basic steps for more habits.
First, make a new table (I’ll call mine [Calc]), and simply put a period as the “name” in the first record. Delete the other default fields, as we won’t be using them.
In the main table where you’re tracking things by date, add a new field that links to the [Calc] table; I’ll call mine {Link2Calc}. Once created, link the first record to that lone “.” record you made in [Calc], the drag-fill that across the rest of the records in that table. Going forward, all new records will also need this link in order for the system to work. The easiest way to do this is to group by this field, which will auto-add that link each time you make a new record.
Also in your main table, add a new formula field with this formula (I’m using your {NO coffee} field from above as an example):
IF({NO coffee}, MID("ABCDEFGHIJKL", {Month #}, 1))
I’ll call this field {NO coffee Month}. For each record with a check in the {NO coffee} field, a letter between A and L will appear in {NO coffee Month}, with the letter corresponding to the month (January = A, February = B, etc).
Moving to the [Calc] table, add a rollup field (I’m calling mine {NO coffee Rollup}, targeting the field with links coming from your main table, choosing your {NO coffee Month} as the field to roll up, and using the following as your rollup aggregation function:
"" & values
This will produce a mashed-together version of all the entries from {NO coffee Month}, which might look something like this:
AAAAAAAAABBBBBBBBBBBBBCCCCCCCCCDDDDDDD...
Next, make a new table; I’m calling mine [Monthly Habit Summary]. Set up the records and the first few fields like this:
Add a {Link2Calc} field in this table as well, with all records pointing to the same “.” record in the [Calc] table.
Next, add a lookup field named {NO coffee Lookup}, using the field that links to the [Calc] table, and pulling the {NO coffee Rollup} field from there. Now the table looks like this:
Finally, add a formula field with the same name as your original habit—in this case, {NO coffee}—using the following formula:
(LEN("" & {NO coffee Lookup})
- LEN(SUBSTITUTE("" & {NO coffee Lookup}, Alpha, "")))
/ Days * 100
In short, this counts the number of times that each month’s corresponding letter appears in the lookup, divides that by the total number of days in the month, then multiplies it by 100 to give you a percentage (change the formatting for the field to use the percentage option). Hide the unnecessary fields, and you now have something like this:
To repeat for each habit:
- Make a new field in the main table that does the alpha-translation from the next habit’s checkbox field.
- Roll that field up in the
[Calc]table. - Add a lookup field in the
[Monthly Habit Summary]table to pull in that habit’s rollup. - Add a new formula field to calculate the percentage.
Many of the new fields in these steps can be duplicated from existing fields, changing field names where necessary. The longest process will be for the first habit. The rest should be doable in about half the time.
