Summarize by groups and only display the sums

[Pch]

Hi @MLAB !

@MLAB

So, there will be one row for each combination of Category and Month (in this case, 12 months and 7 categories equals to 84 rows).

Correct !

@MLAB

Than I must ask, how did you create that Category/Month structure without tediously copying and pasting?

I used the currently disabled canvas button just above the [View of Summary per Month & Category] .

Its action formula is this one:

dbYearMonths.ForEach(
  CurrentValue.WithName(YM,
    dbCategories.ForEach(
      [Summary per Month & Category].AddRow(
        [Summary per Month & Category].dbYearMonths,
        YM,
        [Summary per Month & Category].dbCategories,
        CurrentValue
      )
    )
  )
)

The button first takes the “list of rows” dbYearMonths is (each specific row in the list is represented by CurrentValue) and to each row I give the name YM using WithName() so it can be used later on.

Then, it takes the other “list of rows” dbCategories is (where each specific row is also represented by CurrentValue) and for each row, I ask the button to add a row to the table [Summary per Month & Category] where:

• for the field [Summary per Month & Category].dbYearMonths it should input YM (the current month in the list)
• for the field [Summary per Month & Category].dbCategories it should input CurrentValue (the current category in the list)

Sorry if everything’s not clear … loops are not the most easiest thing to explain …
But the button loop through all the months in dbYearMonths, one by one, and for each month it adds each category from dbCategories .

Just in case, here’s a topic that helped me a lot when I was struggling with ForEach() / FormulaMap()

FormulaMap() : When and where?

@Pch Good discussion. Tossing this in for anyone else having this question. List(2,3,4).FormulaMap( CurrentValue * 2 ) 4,6,8 This is the new list comprised of each value from the original list transformed by the formula invoked in FormulaMap(). FormulaMap() is a forEach() loop that returns values. For each value in a source list, FormulaMap() maps a formula to each one of those values, meaning that it runs that formula on each value in the list. The results are returned in a n…

As for this :

@MLAB

My point is less of boringness and more of consistency, as the Categories list will likely evolve over time and the YearMonth will certainly grow.

I could make some suggestions regarding the consistency, but it depends on your current setup, how you would like/need to add months & categories to their respective tables and also, how you like to interact with your bankstatements table …

There are often more than one way to accomplish anything in Coda