Good evening,
I’ve been working on this problem all day, if anyone has any ideas!
I’m trying to do this:
I have fields value :
(tech::clean :3:0.5:min) (tech::cook :5:0.2:min)
I want a table that displays this and a total for the calc column :
technique
time
coef
cal
clean
3
0.5
4.5
cook
5
0.2
6
Total time cal : 10.5
so i try:
TABLE WITHOUT ID
split(tech, ":")[0] AS technique,
split(tech, ":")[1] AS time,
split(tech, ":")[2] AS coef,
cal
WHERE tech
FLATTEN tech
FLATTEN sum(time+(time*coef)) AS cal
WHERE file.name = this.file.name
But it doesn’t work, so I don’t know how to sum up the whole ‘cal’ column.
TABLE WITHOUT ID
split(tech, ":")[0] AS Techniques,
split(tech, ":")[1] AS temps,
split(tech, ":")[2] AS coef,
timing,
total
WHERE tech
FLATTEN tech
FLATTEN sum((number(split(tech, ":"))[1])+((number(split(tech, ":"))[1])*(number(split(tech, ":"))[2]))) AS timing
FLATTEN sum(number(rows.timing)) AS total
WHERE file.name = this.file.name
but I can’t total the column…
If anyone has any ideas!
thank you
When reading a query it’s sometimes to useful to understand it as if the first line is actually the last. That is read the query line by line, but interpret the TABLE line as the last line of the query. This explains why you don’t get a value in the cal column as it doesn’t really now your time and coef since those are defined in the “last” line of the query.
So try out the following query, and see if that’s getting your closer to your goals:
```dataview
TABLE WITHOUT ID
technique, time, coef, cal
WHERE tech
FLATTEN tech as t
FLATTEN list(split(t, ":")) as parts
FLATTEN parts[0] as technique
FLATTEN parts[1] as time
FLATTEN parts[2] as coef
FLATTEN number(time) * number(coef) as cal
```
Here I do the FLATTEN of each part before I’m trying to using them in any calculation, and then display all the results as the “last” line. This logic then makes sense, if you follow my reasoning on reading the first line as the “last” line.
Do however note that a query by nature only focuses on any given row. So doing sums alongside the actual rows is not easily achievable. To get sums you either need to resort to executing your query within dataviewjs and adding the summed total line (see thread at end for more on this), or dedicate the query to doing the sum and then display it in a separate table:
```dataview
TABLE WITHOUT ID sum(rows.cal) as "Total time used"
WHERE file = this.file
FLATTEN tech as t
FLATTEN list(split(t, ":")) as parts
FLATTEN parts[0] as technique
FLATTEN parts[1] as time
FLATTEN parts[2] as coef
FLATTEN number(time) * number(coef) as cal
GROUP BY true
```
Some more information on how to use dataviewjs in combination with a query to produce a summation line or column totals: