Sum and average of values in a nested tag dataview

[joaosousa]

Hello, folks.

What I’m trying to do

I am trying to sum and average values that are in a nested tag. I will give you an example.

#parent/child/john/0
#parent/child/john/1
#parent/child/mary/2
#parent/child/mary/1

The expect outcome would be a table similar to that:

NAME – AVERAGE
john – 0.5
mary – 1.5

Things I have tried

I was able to get the value using the following query.

TABLE X, Y 
FROM #test
FLATTEN filter(file.etags, (f) => startswith(f, "#parent/child/")) as X
FLATTEN substring(X, length(X)-1) as Y

Resulting in this

and I was able to get the names using the following query.

TABLE A
FROM #test
FLATTEN regexreplace(regexreplace(filter(file.etags, (f) => startswith(f, "#parent/child/")), "#parent/child/", ""),"/\d+", "") as A

But I can figure out how to merge both, when I try they show empty tables or a lot of rows like above.

[holroy]

You’re doing some logic on the separate parts of the nested tags, which in my head triggers the thought of splitting it up, and doing that in a different matter. After some fiddling about I ended with this query:

```dataview
TABLE et, p, value, rows.value, sum(rows.value), average(rows.value)
FROM #test
FLATTEN file.etags as et
FLATTEN list(split(et, "/")) as p
FLATTEN number(p[3]) as value
WHERE p[0] = "#parent" AND p[1] = "child"
GROUP BY p[2] as Person
```

This is actually two queries in one, so try it first without the GROUP BY line to see what the values of et, p, and value are before the grouping occurs. Then you can add the GROUP BY line, and see how these values is moved into the rows object, and you can use them for calculations. (You could/should also try it with adding the columns rows.et and rows.et, and maybe even with adding a key column, just to see what’s available)

[joaosousa]

@holroy

```dataview
TABLE et, p, value, rows.value, sum(rows.value), average(rows.value)
FROM #test
FLATTEN file.etags as et
FLATTEN list(split(et, "/")) as p
FLATTEN number(p[3]) as value
WHERE p[0] = "#parent" AND p[1] = "child"
GROUP BY p[2] as Person

Hello, holroy.

Amazing query, it is what I was expecting, I just delete some columns and change the average(row.values) to sum/length. In general you give me what I needed, thanks!

TABLE rows.value as Values, sum(rows.value)/length(rows.value) as Average
FROM #test 
FLATTEN file.etags as et 
FLATTEN list(split(et, "/")) as p 
FLATTEN number(p[3]) as value 
WHERE p[0] = "#parent" AND p[1] = "child"
GROUP BY p[2] as Person

[holroy]

Doesn’t average(rows.value) produce the sum(rows.value) / length(sum.value) in your case? It does in my simple test cases…

Update: If you’ve got null values in your list, you could possibly get differing values, see also: average()

[joaosousa]

Great. Now I know why it wasn’t working the average().

Thanks, Holroy