I am creating a canvas formula. The goal - average of the sum of a single year within a column. Is it better to go with the average formula or filter formula?
Average formula example - Average(year 1 view of table.column month 1).sum(),(year 1 view of table.column month 2).sum(), (year 1 view of table.column month 3).sum())
Filter formula example - year 1 view of Table.column.filter(month=jan AND month=feb AND month = mar).sum()/3
These results will need to include 12 months at the most.
Both formulas are different and if I understood the requirement right, neither is really correct.
How does your table look like, and average of what sums exactly do you need to calculate?
Let’s assume your table is a table of sales where one row is one sale; it has Date and Amount, and out of Date you trim it down to a Month. And let’s assume, you want to:
For each month get a sum of sales for that month
Get the average of those sums.
The best solution, and the most performant one, would be to introduce a separate table, Sale Months, where each row would map a Month to a calculated Total of sales for that month. Then to get the average you’d just filter that sums table for a range of months and get the average. You’ll have to make sure that there are rows added for every month — e.g. run a monthly automation ensuring there are all necessary months in that table.
However, if you absolutely want to avoid making a separate table, it also can be solved but the formula is pretty complicated and not the most performant, as it will recalculate on the addition of every sale.
Please let me know if that makes sense, and also what your table is (best is to share the doc), and what the requirement is.
@Paul_Danyliuk, Thanks for you reply. Here is a link to a sample doc I created. It does not have any data but hopefully it will have enough structure to give you an idea of what I am trying to do.