Sum, group and conditional calculation

What I’m trying to do

Having this 3 frontmatter (as a minimal example)

A: Group1
B: work
C: 5

A: Group1
B: done
C: 4

A: Group2
B: work
C: 2

Things I have tried

what I have working:


Group by

which gives me:

Group1 9
Group2 2

This result is fine.

Now i want to have an additional column with the sum of C for each A if B = “done”. i tried to use choice( B=“done”, C, 0) which works correct if the table lists all entries.

I need a hint to combine this choice (or soemthing else) with the sum/group by.

You’d need a sum of a map of a filter of those elements. Something along the lines of:

      r => r.B = "done),
    m => m.C

You can collapse it together if you feel like it. I just used indentation to show the steps starting from the innermost action:

  • Filter the rows, and return the rows where the variable B is set to “done”
  • From these filtered rows, select only the C column
  • Sum those C elements

Note that we do the filter operations on the rows to keep the possibility open for later selection of just the C column.

1 Like

Many thanks, it works well ( I need some time to understand it but it works).

Just adding each element as separate columns might help understanding it. That is add the filter() on one column, and then the map( filter () ) combination in another column.

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.