Dataview, how to make table of read book by month and year as sum?

Help
1

What I’m trying to do

I have one note fora each book that I have read with corresponding yaml data (title, start end).
start and end are in YYYY-MM-DD format
There are some additional field but they are not important for this question.

I would like to have table where columns would be months like number 1, 2 … 11, 12, row would be years and each filed is populated with.

Things I have tried

I do know that I can access months by end.month and year by end.year.
But I have problem with even doing sum per years like

TABLE sum(title)
FROM "books"
GROUP BY end.year
2

The sum() function tries to calculate the sum of its parameters, so the sum of title means it tries to add up all titles. If you’re looking for a count of how many books you’ve read, you rather want to use length() to give the length of the list of books you’ve read.

In addition, when you do GROUP BY any field, the other fields are moved into lists under the rows object. So in your example, there would be a list of titles stored within rows.title.

Combining this into one query we get:

```dataview
TABLE length(rows.title)
FROM "books"
GROUP BY end.year
```

The other part of your request, to get how many which was read each month, we need to massage the lists further, and the tool to use for that would be filter. So maybe something like the following would work:

```dataview
TABLE 
  length(rows.title) as YearCount,
  length(filter(rows.end, (e) => e.month = 1)) as January,
  length(filter(rows.end, (e) => e.month = 2)) as February,
  ...
  length(filter(rows.end, (e) => e.month = 12)) as December
FROM "books"
GROUP BY end.year
```

Query is untested, and it could be flawed related to what happens if some of the months don’t have any end dates. But fill it out for all months, and try it out and see what it returns. (Hopefully, there is not that much errors in my query. :slightly_smiling_face: )

1 Like
3

this is working as I am expecting.
Just one more question:
Is there some way to display - instead of 0 in month/year where there was zero/0 read book?
It is easier for quick look.

4

found it
replace(string(length(filter(rows.end, (e) => e.month = 5))), "0", "\-") as "5",
is there some good code editor for this ? :slight_smile:

5

Not quite sure I understand what you want here, but if you want to replace the 0 with something, you could try something like:

default(length(filter(rows.end, (e) => e.month = 1)), "none") as January

This should present you with the text “none”, if there is no output (or a falsy value) from the preceding expression. You could of course also just use "" or " " or similar if you feel like that.

6

So opposed to what’s usually happening, the default requires an actual null value, and not only a false value for it to trigger, so my previous response fails miserably. The following is working and is an alternative to your replace approach:

```dataview
TABLE WITHOUT ID 
  choice(Jan, Jan, "\-") as January,
  choice(Feb, Feb, "\-") as February,
  choice(Mar, Mar, "none")  as March
FROM "books"
FLATTEN length(filter(rows.end, (e) => e.month = 1)) as Jan
FLATTEN length(filter(rows.end, (e) => e.month = 2)) as Feb
FLATTEN length(filter(rows.end, (e) => e.month = 3)) as Mar
GROUP BY end.year
```

You need to fill in the blanks, but you get the gist of the idea. Here we store the number of books read in a temporary variable, like Jan, and then when we display it check for whether it’s a truthy value (aka not zero), and if so we display the value, and if not either the text "\-" or "none". The former needs the backslash in order for it not to show up as a bullet point (at least in my theme).

7

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