Dataview - multiply two fields within a file, then get the sum for all files

Help
1

I’m trying to build a system for tracking albums I listen to. This is an example of what each album’s frontmatter looks like:

listenDate:: [[2022-12-09]]
artist:: [[Billie Holiday]]
year:: 1958
genre:: jazz, pop
runtime:: 42
rating:: 65
listens:: 1

I have another note for tracking the statistics related to the albums. This is the main dataview table:

table without id
	length(rows.rating) as Count,
	sum(rows.rating) / length(rows.rating) as "Average Score",
	sum(rows.runtime) / length(rows.runtime) as "Average Runtime (mins)"

from "music/albums"

group by file.folder

What I’m Trying to Do

I would like to have a column in the dataview table which multiplies runtime by listens for each note, then displays the sum of this value for each note. This would allow me to see the overall time I have spent listening to music.

What I’ve Tried

table 
	sum((row.rating) * row.listens) as Time
from "music/albums"

This lets me calculate the total time for each individual album, but I want the sum of this calculation for all albums.
When I add group by file.folder to this table, nothing is rendered in the Time column.

One idea I have is to have a table which lists the total time from each album and then query that table from the master one to get the sum, but I’m not sure if that’s possible.

1 Like
2

To test purposes, use this query:

TABLE WITHOUT ID
	length(rows) as Count,
	sum(rows.rating) / length(rows.rating) as "Average Score",
	sum(rows.runtime) / length(rows.runtime) as "Average Runtime (mins)",
	rows.runtime as runEachFile,
	rows.listens as ListensEachFile,
	map(rows, (r) => r.runtime * r.listens) AS runXListensEachFile,
	sum(map(rows, (r) => r.runtime * r.listens)) AS TotalTime
FROM "music/albums"
GROUP BY file.folder
1 Like
4

Awesome, thank you!

Is there a way I could prevent runEachFile, ListensEachFile, runXListensEachFile from displaying as columns in the output?

5

I started saying

This means I added extra columns to you see step by step what was done.
sum(map(rows, (r) => r.runtime * r.listens)) AS TotalTime is the logical conclusion of the previous three columns expressions.

In dataview, if you want to exclude one column, you just need to delete the expression related with that column (sorry, but this is a very basic thing!).

TABLE WITHOUT ID
	length(rows) as Count,
	sum(rows.rating) / length(rows.rating) as "Average Score",
	sum(rows.runtime) / length(rows.runtime) as "Average Runtime (mins)",
	sum(map(rows, (r) => r.runtime * r.listens)) AS Total
FROM "music/albums"
GROUP BY file.folder
6

Oops, now I feel dumb. I didn’t read thoroughly enough and thought that the mapping was calling the previous lines, didn’t realize they were just there for show. I’m new to Dataview lol.

Thanks for your help.

1 Like
7

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