I am currently trying to create a summary table for expenditures for multiple fiscal years that pulls from my master table. The master table format is like so:
I want the summary table to show expenditures by Criteria and then by total for each department.
I cannot create a view of the table and then group because the summarize “sum” function cannot create percent change on the summarized year to year totals. Therefore, I created a view of the master with a org as a lookup column. I am trying to pull over the data for each department but, as you can briefly see from the master, each department has multiple cost centers. So, for the formula on my view table, I was looking to do the following formula for each fiscal year.
I am getting hosed with “Thisrow.org” piece of the formula. If I hard enter the org number, the formula works but it is not dynamic and does not change for each row. Does anyone know what the issue could be?
It seems like it is one of those Coda details that consistently trips me up as well.
The little icons after the names in your formula is important. It conveys that the Master i a table, and also that ORG, and thisrow.org deals with rows, not unique values. When you hardcode an entry, you hardcode a value, not a row reference. (At least, I think this is what is happening here.
Is it possible you could share the doc, or an anonimised copy of the relevant tables? It will help the community to look into this more accurately.
Hey Frank, Lena from Coda Support here Thanks for sharing that example doc.
I think I found a solution for you! So instead of Master.Filter(ORG=thisRow.Org), you’ll actually want Master.Filter(currentValue=thisRow.Org). Because thisRow.Org contains entire rows from the Master table, so you want to reference the entire row in your filter formula (via “CurrentValue”) rather than a specific column (like “ORG”). Here’s a screenshot to show you an example.
I believe that should give you the values you’re after. But you might still see a red line and some error msging when you open up the formula builder. This is stemming from the formula in your FY19 column (in your Master table). The formula theoretically outputs multiple numbers, but the number column wants/expects only single values. You’ll notice that if you add .first() to the end of your FY19 column formula (like shown below), that red error in your original formula should resolve.
Thanks for sharing that example doc.
