I collect this data from certain forms that are assigned to each Month&Year. Each row has 4 values that I’ll need later to calculate the mean. The problem is that I have to do the calculation filtered per each team (last row), company and per each Month&Year. And I would like to do it escalable so the best way is to find a calculation method to achieve it instead of divide each Month&Year on tabs manually. Even doing that I would need to divide per each team and company.
So the calculation per each team and Month&Year is to (1) =COUNTIF(range;">0") and to (2) =SUM(range). So you can take the mean is you divide (2) / (1). I know that you can calculate the mean by doing a roll-up on Glide but I’ll need to filter the data previously.
Is there some angel that could help me with this? Thank you a lot!
You just need a template column that combines the Team, Year & Month, and then use that template to build a multi-relation, and finally use a rollup column to do a sum through the relation. That’s it.
Thank you @Darren_Murphy, seems so easy for you haha but I don’t visualize it. What you mean about a template column that combines the Team, Year & Month?
In the above example I’m joining year, month and two other columns.
Using templates in this way in combination with relations, you can essentially slice and dice your data in any way that you want.
Correct, yes.
It depends how your tables are setup. You could just create a self-referencing relation and work with that, but that can get a bit messy. I usually have a separate Glide table with just a single row that I use as a working table for this sort of thing.
Just to expand a bit on that, when I say it depends, it really also depends how you want to present the rollup data. Let’s say for example you want to allow the user to select a team, and then show the summarised data for that team, broken down by month for the current year. You could do that something like this:
In a separate table
– Add 12 rows, with one row for each month of the year, and fill that with your month names (or numbers, whatever)
– Add a math column that gives you the current year (Year(Now))
– Add a user specific column to hold the team selection (this one should be populated by a choice component that presents the user with a list of teams)
– Add a Single Value column that expands that choice to all 12 rows
– Now create a template column that joins the year, month, and selected team (the single value column)
– Finally, create a relation that joins that column to the same template column that you created in the source table.
From there, you can do whatever rollups you need through that relation. Obviously for your relation to work, you need to ensure that the template formats are identical. ie. same format for month and year, etc.
