Sum, group and conditional calculation

[DaDirnbocher]

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:

TABLE 
	sum(rows.C),

Group by
	A

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.

[holroy]

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

sum(
  map(
    filter(
      rows, 
      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.

[DaDirnbocher]

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

[holroy]

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.