Rollup to count check marks in a particular month

[Emma_MH]

Hi there,

I’m trying to gerryrig a pivot table type situation. Basically I made a habit tracker and I want to keep a monthly log of how many of each habit I tracked that month (ideally by percentage but willing to do it by number). I have it set up so that each day is the “Name” of the record and each column is a habit with a check mark. I know check marks are supposed to equal 0 and 1 in terms of formulas but I was trying to use Ifs to make it conditional so that it would tell me the count for each month (ie If({Month #}={Month}, Sum({NO coffee})) with both Month # and NO coffee being from the main table that I’m trying to summarize. Everything I’ve read in the documentation seems to indicate you can cross-reference columns from the table you’re rolling up and yet those options don’t show up when it pre-populates options for the formula. I know I can technically view all this just through filters and grouping but I was hoping for something cleaner that would be easy to visually see improvement.

[Elias_Gomez_Sai]

What Tables and Fields do you have?

[Justin_Barrett]

You might consider making a new view for this. In that view, group your records by the {Month #} field, which should separate each month. Then set the summary option for your checkbox fields to “Percent Checked.” Each month group will have its own total for those fields.

EDIT: Oops! That’s what I get for not reading to the very last line. You said you’re looking for an alternate option to grouping. That’s definitely doable, as I’ve done it myself, though it’s trickier because of the month separation. I’ll have to work on this a bit more.

[Justin_Barrett]

How many habits are you tracking? I’m working on something that may do the trick, but part of the design depends on how many unique things you want to count.

EDIT: Never mind. The count doesn’t matter after all. This will work with any number of habits, though obviously it’s a bit more work the more you have. Anyway, I’ll start a new reply with my solution.

[Justin_Barrett]

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:

1. Make a new field in the main table that does the alpha-translation from the next habit’s checkbox field.
2. Roll that field up in the [Calc] table.
3. Add a lookup field in the [Monthly Habit Summary] table to pull in that habit’s rollup.
4. 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.

[Emma_MH]

Wow! Omg you’re amazing! THank you so much for figuring that craziness out!