Sum of calculated fields

[BaumiCoder]

Hello,

I want to make a TABLE Query with a sum() of an "calcuated field". The "calculated field" in my case is a product of two fields of each row. It is like a sum of products. My Query

TABLE sum(rows.ECTS) AS "ECTS Sum", 
truncate(string(sum(rows.ECTS * default(rows.grade,0)) / sum(rows.ECTS)), 3, "") AS "grade average"
FROM outgoing([[#]])
GROUP BY grade = null AS "current semester"

didn't work. I got this error:

Dataview: Every row during final data extraction failed with an error; first 3:

            - No implementation found for 'array * array'
- No implementation found for 'array * array

This is an sample Vault for my problem: SumTests.zip
There is a little example for the formula in it.

Is there a way to archive this calculation with dataview? I would be thankful for any help.

[blacksmithgu]

Hmm, I think this may require DataviewJS currently, since there isn't an operation to zip together arrays. Might be worth adding a zip or combine function to combine arrays via an operator (like *), or maybe to add vectorized math operations to arrays.

[BaumiCoder]

Hello @blacksmithgu,

I try to implement the calculation with DataviewJS. I have programming experiences but no experiences with JS.
A group by seems to complicated for me. So I try to only implemented a query which only calculated the grade average. I try this query

dv.span(
dv.pages("outgoing([[#]])")
.filter(b => b.grade != null)
.map(b => b.ECTS * b.grade)
.reduce((a, b) => a + b)
/ 
dv.pages("outgoing([[#]])")
.reduce((a, b) => a.ECTS + b.ECTS)
)

But I got the error "dv.pages(...).filter(...).map(...).reduce is not a function". Is the reduce function not available in DataviewJS?

I think an Dataview function would be helpful. I my opinion a real query language is significantly simpler then JS.

[Ji11ard]

I think you can use FLATTEN to make your calculated value before grouping - then sum it like any array:

TABLE sum(rows.Calculation) AS "ECTS Sum", 
            sum(rows.Calculation) / length(rows) AS "grade average"
FROM outgoing([[#]])
FLATTEN (ECTS * default(grade, 0) AS Calculation
GROUP BY ...

[BaumiCoder]

Thank you @Ji11ard. It works with FLATTEN. I had already tried it with flatten, but I hadn't understood how to use it.
This query now does exactly what I wanted:

TABLE sum(rows.ECTS) AS "ECTS Sum", 
    truncate(string(sum(rows.Calculation) / sum(rows.ECTS)), 3, "") AS "grade average"
FROM outgoing([[#]])
FLATTEN (ECTS * default(grade, 0)) AS Calculation
GROUP BY grade = null AS "current semester"