Help with Obsidian Charts and Dataviewjs

Sorry for the delay but I have been swamped at work and only managed to get back to this today.

So I managed to create most of the queries I need with DQL.

They are basically just getting and counting notes with specific inline fields. I give a few examples below.

Example 1
Getting tickets by domain

TABLE Domains AS Count
WHERE contains(file.name,"INC") OR contains(file.name,"SCTASK") AND !contains(Domains, null)
FLATTEN Domains
GROUP BY Domains
FLATTEN length(rows.file.link) as Domains

This returns

If anyone knows a way to rename that first column please let me know.

Example 2
Getting tickets based on creation date

Table Created
WHERE contains(file.name,"INC") OR contains(file.name,"SCTASK") AND Created
GROUP BY dateformat(Created, "MM-yyyy") AS Created
FLATTEN length(rows.file.link) as Created

This returns

I changed the way date is entered in the templates to accomodate for the way Obsidian handles the date field.

As I said I divided this in three steps:

  • Create normal dataview queries
  • Move the queries to dataviewjs
  • Integrate with Obsidian Charts

I am having issues with two queries.

Issue 1
Getting a table with the days between Created and Closed Dates

This is how far I managed to get:

Table (Closed - Created).days AS Difference
WHERE contains(file.name,"INC") OR contains(file.name,"SCTASK") AND !contains(Closed, null) AND !contains(Created, null)  

which returns

Anything I try after this messes up everything. I have no idea how to group the returned data. What I want is a table with a column that says Days and has the values that exist in the notes and a second column with how many times that “duration” exists in the notes.

For instance, I want to be able to see how many tickets took 5 days to close.

Issue 2
Getting a table with how many Created and Closed tickets exist per month.

This is how far I got:

TABLE Created, Closed
WHERE contains(file.name,"INC") OR contains(file.name,"SCTASK") AND !contains(Created, null) AND !contains(Closed, null)

which returns

I will keep trying but any help is appreciated.

Sorry for the long post.

1 Like

Figured out a way to make the first column have a custom name that reflects what I want with:

TABLE WITHOUT ID row.key AS "Status Type", Status AS "Status Count"
WHERE contains(file.name,"INC") OR contains(file.name,"SCTASK") AND !contains(Status, null)
FLATTEN Status
GROUP BY Status
FLATTEN length(rows.file.link) as Status

This returns

Ok. So, I’m stuck with something I don’t even know if it’s possible.

Regarding Issue 2 I need to create the first column with the Month Name. I -don’t want to get this from the notes, I want to have a column with all the Months and “map” the Created and Closed date to each Month so basically I get the number of created and closed tickets by Month.

I have tried a lot of things unsuccessfully.

Again, any help is highly appreciated.

The trick to making this happen is to move the calculation from the column definitions into a FLATTEN field, like the following:

```dataview
TABLE Difference
FLATTEN (Closed - Created).days as Difference
WHERE
  contains(file.name,"INC") OR 
  contains(file.name,"SCTASK") AND 
  !contains(Closed, null) AND 
  !contains(Created, null)  
```

Since the difference is now a calculated field, you’re also allowed to do GROUP BY Difference, and then do a length(rows.Difference).

Two temporary queries to show this logic

Pop the code below into a note of its own, and see the magic unfold. :slight_smile:

## My table of created & closed
```dataview
TABLE WITHOUT ID tmp[0] as File, tmp[1] as Created, tmp[2] as Closed, Difference
FLATTEN list(
  ["INC01", "2023-01-01", "2023-01-11"],
  ["INC02", "2023-01-05", "2023-01-15"],
  ["SC01", "2023-01-13", "2023-01-21"],
  ["SC02", "2023-01-21", "2023-02-11"],
  ["SC03", "2023-02-01", "2023-02-04"],
  ["INC03", "2023-02-03", "2023-02-11"],
  ["INC04", "2023-02-01", "2023-02-11"],
  ["INC05", "2023-03-01", "2023-03-09"],
  ["INC06", "2023-03-21", "2023-03-29"],
  ["SC05", "2023-03-01", "2023-03-04"]) as tmp
FLATTEN (date(tmp[2]) - date(tmp[1])).days as Difference
WHERE file.name = this.file.name
SORT Difference
```

## Grouped version of Differences
```dataview
TABLE length(rows.Difference)
FLATTEN list(
  ["INC01", "2023-01-01", "2023-01-11"],
  ["INC02", "2023-01-05", "2023-01-15"],
  ["SC01", "2023-01-13", "2023-01-21"],
  ["SC02", "2023-01-21", "2023-02-11"],
  ["SC03", "2023-02-01", "2023-02-04"],
  ["INC03", "2023-02-03", "2023-02-11"],
  ["INC04", "2023-02-01", "2023-02-11"],
  ["INC05", "2023-03-01", "2023-03-09"],
  ["INC06", "2023-03-21", "2023-03-29"],
  ["SC05", "2023-03-01", "2023-03-04"]) as tmp
FLATTEN (date(tmp[2]) - date(tmp[1])).days as Difference
WHERE file.name = this.file.name
GROUP BY Difference
```

Note that in addition to the wanted logic, I’m faking the input data into the tmp list, but I reckon you can see the similarity with your own query.
Output from these queries:

I’m a little unsure on what result you want out of this. Do you want to list any ticket with either a Created or Closed within that month? But you don’t want to rely on the actual dates in the tickets. It’s somewhat easy if you do just one of them, but I’m not sure what’s the best way to handle both of them.

Issue 2 - Alt A - Just “Created” in DQL

The following will also apply for the Closed, so using the same trick as in the previous query the following should work:

```dataview
TABLE length(rows.Created)
FLATTEN dateformat(Created, "yyyy-MM") as CreatedMonth
WHERE contains(file.name,"INC") OR contains(file.name,"SCTASK") AND !contains(Created, null)
GROUP BY CreatedMonth
```

However, we can’t duplicate this logic directly, since both the fields should be able to be not set, and it’s not a given that they’re created and closed within the same month.

Test query for Issue 2 on Created
```dataview
TABLE length(rows.Created)
FLATTEN list(
  ["INC01", "2023-01-01", "2023-01-11"],
  ["INC02", "2023-01-05", "2023-01-15"],
  ["SC01", "2023-01-13", "2023-01-21"],
  ["SC02", "2023-01-21", "2023-02-11"],
  ["SC03", "2023-02-01", "2023-02-04"],
  ["INC03", "2023-02-03", "2023-02-11"],
  ["INC04", "2023-02-01", "2023-02-11"],
  ["INC05", "2023-03-01", "2023-03-09"],
  ["INC06", "2023-03-21", "2023-03-29"],
  ["SC05", "2023-03-01", "2023-03-04"]) as tmp
FLATTEN dateformat(date(tmp[1]), "yyyy-MM") as CreatedMonth
WHERE file.name = this.file.name
GROUP BY CreatedMonth
```

With output:

Issue 2 - Alt B - Both “Created” and “Closed” using DQL

The gist of this query is that we create both CreatedMonth and ClosedMonth, and then create a new column to preserve the month, and what happened this month. So if the entry is ["2023-01", 1, 0] it’s a created ticket, and if the entry is ["2023-01", 0, 1] it’s a closed ticket.

These two values are then split into separate rows, (so now each original row is two rows, one for created and one for closed), and group by the month in the first column. Now we can simply(?) sum up the values belonging to either the created or closed category.

Alt 2 queries
### Dual-month column
```dataview
TABLE WITHOUT ID theMonth, CreatedMonth, ClosedMonth, tmp[0] as File
FLATTEN list(
  ["INC01", "2023-01-01", "2023-01-11"],
  ["INC02", "2023-01-05", "2023-02-15"],
  ["SC01", "2023-01-13", "2023-01-21"],
  ["SC02", "2023-01-21", "2023-03-11"],
  ["SC03", "2023-02-01", "2023-02-04"],
  ["INC03", "2023-02-03", "2023-03-11"],
  ["INC04", "2023-02-01", "2023-02-11"],
  ["INC05", "2023-03-01", "2023-04-09"],
  ["INC06", "2023-03-21", "2023-03-29"],
  ["SC05", "2023-03-01", "2023-04-04"]) as tmp
FLATTEN dateformat(date(tmp[1]), "yyyy-MM") as CreatedMonth
FLATTEN dateformat(date(tmp[2]), "yyyy-MM") as ClosedMonth
FLATTEN array([CreatedMonth, 1, 0] , [ClosedMonth, 0, 1]) as theMonth
WHERE file.name = this.file.name
```

### Split up again into Created & Closed
```dataview
TABLE
  sum(map(rows.theMonth, (r) => r[1])) as Created,
  sum(map(rows.theMonth, (r) => r[2])) as Closed
FLATTEN list(
  ["INC01", "2023-01-01", "2023-01-11"],
  ["INC02", "2023-01-05", "2023-02-15"],
  ["SC01", "2023-01-13", "2023-01-21"],
  ["SC02", "2023-01-21", "2023-03-11"],
  ["SC03", "2023-02-01", "2023-02-04"],
  ["INC03", "2023-02-03", "2023-03-11"],
  ["INC04", "2023-02-01", "2023-02-11"],
  ["INC05", "2023-03-01", "2023-04-09"],
  ["INC06", "2023-03-21", "2023-03-29"],
  ["SC05", "2023-03-01", "2023-04-04"]) as tmp
FLATTEN dateformat(date(tmp[1]), "yyyy-MM") as CreatedMonth
FLATTEN dateformat(date(tmp[2]), "yyyy-MM") as ClosedMonth
FLATTEN array([CreatedMonth, 1, 0] , [ClosedMonth, 0, 1]) as theMonth
WHERE file.name = this.file.name
GROUP BY theMonth[0] as Month
```

Please do run these, and verify that you understand what’s happening. The first query (top part) should look like:

Note how each “file” has two rows, one for the creation and one for the closure of the tickets)

The second query should look like:

Note that in the second query the changes are the column output, and the addition of the GROUP BY theMonth[0] as Month line.

If you’re having a hard time understanding the second query, try adding rows.theMonth as a column (and view the result in a theme other than Minimal which shows the proper list levels)

Understanding the sum statement

The data input from rows.theMonth are in this format:

  • (empty)
    • 2023-01
    • 1
    • 2
  • (empty)
    • 2023-01
    • 0
    • 3

So the map(rows.theMonth, (r) => ... ) splits up this multi-level list on the first level (the empties…), and then the r[1] selects an element from the second level list (where r[0] is the first element, aka the month, and r[1] is the second element, which is the indicator of it being created or not. r[2] is then the third element indicating a closed ticket).

Then the sum( ... ) simplies sums up the corresponding list. So in this case it does sum( [1, 0] ) -> 1 for Created, and sum ( [2, 3] ) -> 5 (note that the 2 and 3 are just for illustration (so their origin). In the query they’re all 1’s or 0’s)

Please look through the queries of this second alternative, before you try to modify your own queries. I can’t easily test them for you, so the following is an attempt to adapt them to this logic, but it might very well contain some errors which I hope you’ll figure out.

```dataview
TABLE
  sum(map(rows.theMonth, (r) => r[1])) as Created,
  sum(map(rows.theMonth, (r) => r[2])) as Closed
FLATTEN dateformat(Created, "yyyy-MM") as CreatedMonth
FLATTEN dateformat(Closed, "yyyy-MM") as ClosedMonth
FLATTEN array([CreatedMonth, 1, 0], [ClosedMonth, 1, 0]) as theMonth
WHERE  contains(file.name, "INC")
   OR contains(file.name, "SCTASK")
  AND !contains(Created, null) 
  AND !contains(Closed, null)
GROUP BY theMonth[0] as Month
```

The changes I’ve made are adding the two sum columns, the three FLATTEN rows, and then reformatted the WHERE clause (but not changed it), and added the GROUP BY row.

If something goes astray, try to remove the GROUP BY and sum columns, and change the TABLE line into something like TABLE theMonth, Created, CreatedMonth, Closed, ClosedMonth and see what looks wrong…

Question on boolean precedence

In both these queries you’re doing combination of OR and AND without using parenthesises. This leaves those boolean expression somewhat vulnerable for the precedence of the operators, and potentially strange results. It’s common to just use one type of operators without any operators like: test AND test2 AND test2, or alternatively test1 OR test2 OR test3. Both of these make sense, and are easily understood.

When you do test1 OR test2 AND test3 AND test4 it’s not a given what happens in the various cases, and you’re usually much better of doing something like (test1 OR test2) AND test3 AND test 4 where you require either test1 or test2 to be true, and both test3 and test4 to be true.

A simplified case of test1 OR test2 AND test3, could in some programming languages be short cut if test1 was true, since it’s OR’ed with the the rest. I tested this particular case within Obsidian, and it is evaluated as (test1 OR test2) AND test3 AND test4, but I would in most circumstances rather use this syntax, rather than the one without the parenthesis.

So for the latter query I would type it out as:

WHERE 
   ( contains(file.name, "INC) OR
     contains(file.name, "SCTASK") )
  AND !contains(Created, null)
  AND !contains(Closed, null)

Another version of the same query (I think :slight_smile: ) is

WHERE filter(list("INC", "SC"), (i) => contains(file.name, i))
  AND all(list(Created, Closed))

Not sure if the first is better or not, but it’s an alternate syntax which could come in handy if you got many alternatives.

This construct demands a truthy value in all elements in the list, see Functions - Dataview . Other variants include none and any, which respectively demands all to be non truthy (for example null), or any one of them being truthy.


So that should cover both issues, but I’m not entirely sure whether that second issues has the best solution as is. It could possibly be better to do that using dataviewjs, which would require a little javascript, but would potentially run smoother/faster. I’ve not typed out that solution though.

And a tip or two at the end to clarify your boolean expression, so that both Obsidian and humans reading your expression agrees on what is expected.

Thank you so much!!!

It works like a charm.

Regarding Issue 1 it makes total sense. Should’ve thought of that.

In Issue 2 after fixing the typo from FLATTEN array([CreatedMonth, 1, 0], [ClosedMonth, 1, 0]) as theMonth to FLATTEN array([CreatedMonth, 1, 0], [ClosedMonth, 0, 1]) as theMonth it works perfectly.

If I’m being honest I have no idea why though.

Please help me understand.

sum(map(rows.theMonth, (r) => r[1])) as Created

So this basically sums all the rows of…what?
The theMonth part does that work like a variable declaration?
The (r) => r[1] is assigning the sum to column 1 right? Assuming it starts in 0. Why is it called r then?

The FLATTEN statements are actually the only thing that makes sense to me lol.

I had no idea that you could specify the column with [0].

Regarding precedence you’re absolutely right I fixed it with the first example you gave putting parenthesis around the OR but I was wondering if you could explain a little better what the second one does.

If you could help me understand I would really appreciate it.

Now with all these done it’s time to move to dataviewjs so I can integrate with Obsidian Charts.

Basically what the trickery above does is counting the lines having a CreatedDate in that month.

You correctly changed my typo, so that we get either a list of ["2023-01", 1, 0] if it’s a created date, or ["2023-01", 0, 1] if it’s a closed date. So summing up the one’s, is in effect counting the number of those entries.

The FLATTEN ... as theMonth works indeed as a variable declaration part, and in addition it splits each ticket into two rows. Maybe the following will help you understand some of the magic in the FLATTEN, see Dataview query for all tasks in folder with custom status - #5 by holroy

The fuller explanation of the sum line is as follows:

  • sum( ... ) – Sum all the values in the list within here
    • map( ... , ...) – This function will go through a list, the first parameter, and apply a function, the second parameter, to output another list. We want this to go from a complex list of dates and number, into a single list of numbers, so we do:
      • ( rows.theMonth, ... ) – Use the list of rows.theMonth, and apply the function (as discussed in next item) to each of them. Each of the rows in this list are a list of its own in the format ["2023-01", 1, 0] (or the latter switched around indicate created vs close month)
      • ( ... , (r) => [1] ) – We need to have a name to refer to each row, this is chosen (by me) to be (r). When looking for the created tickets, we need to check the second element (not the date), and since we start indexing on 0, the element we want is r[1]. (For closed tickets, we want the third element, so this then becomes r[2])
  • as Created – The final calculation of the sum of the mapped entries are then stored in another variable, here called Created

Hopefully this make a little more sense, as I’m not sure how to better explain it.

Thank you very much for your explanation. I managed to go a step further and I already integrated the queries with Obsidian Charts by using something like:

   const rawData = await dv.query('TABLE sum(map(rows.theMonth, (r) => r[1])) as Created, sum(map(rows.theMonth, (r) => r[2])) as Closed FLATTEN dateformat(Created, "yyyy-MM") as CreatedMonth FLATTEN dateformat(Closed, "yyyy-MM") as ClosedMonth FLATTEN array([CreatedMonth, 1, 0], [ClosedMonth, 0, 1]) as theMonth WHERE (contains(file.name, "INC") OR (contains(file.name, "SCTASK"))) AND !contains(Created, null) AND !contains(Closed, null) GROUP BY theMonth[0] as Month');
   
   const rows = rawData.value.values;
   
   const chartData = {
       type: 'bar',
       data: {
           labels: rows.map(x => x[0]),
           datasets: [
               {label: 'Created', data: rows.map(x => x[1]), backgroundColor: ['#475263']},
               {label: 'Closed', data: rows.map(x => x[2]), backgroundColor: ['#A07669']},
           ],
       },
   }
   
   window.renderChart(chartData, this.container);

which generates the chart

I need one last query that is giving me a hard time and I don’t even know if it’s possible. I need to get all created and modified notes daily for a month. If the note is modified on more than one day it needs to be counted, not just the last time it was modified. It should also only count one modification per day.

I am still trying to understand the logic needed for the query so any help is appreciated.

Getting the creation date is easy, that’s just file.cday. Getting the last modified date is also easy using file.mday.

However, there is no record in the file system (or Obsidian to my knowledge) of when it has else been modified. There is a theoretical method of setting up scripts running every night before midnight registering (and storing) if any file has been modified during that day, but that does require a stuff running regularly outside of Obsidian.

At least, I’ve not heard of any Obsidian plugin which is capable of running once every day to store information like which files has been modified during that day into some kind of log. And if you don’t log it on that day, the last value of file.mday will be ignored on the next modification, so you might want to reconsider how much you want this kind of logging.

I was wondering if you could help me.
I am using the following query

TABLE 
sum(map(rows.theDay, (r) => r[1])) as Created,       	
sum(map(rows.theDay, (r) => r[2])) as Closed 
FLATTEN dateformat(Created, "yyyy-MM-dd") as CreatedDay 
FLATTEN dateformat(Closed, "yyyy-MM-dd") as ClosedDay 
FLATTEN array([CreatedDay, 1, 0], [ClosedDay, 0, 1]) as theDay 
WHERE (contains(file.name, "INC") OR (contains(file.name, "SCTASK"))) AND !contains(Created, null) AND !contains(Closed, null) AND dateformat(Created, "yyyy-MM") = "2022-10" 
GROUP BY theDay[0] as Day

This is based on the example you provided. The issue I’m having is that because I am grouping by created date I see tickets that should be counting as closed in november are appearing in october.

Is there a way to group simply by day and not use the created or the closed date?

That query is limited to "2022-10", which it most likely shouldn’t be. See the end of the WHERE clause.

So one question is rather, for which month do you want it to display stuff? The current month, a month related to the note you’re in, or possibly a month picked from a field somewhere?

Another option could be to remove the limitation to that month in general, and then consider adding a new GROUP BY or similar to allow it show this for all months…

I want to be able to get the notes for every specific month individually. I want to be able to see all the notes created and closed per day on a specific month.

Then you would need to just change that 2022-10 into the month you’re targetting for this particular output.

One way to do that would be to have the following line (or similar in the frontmatter) for the note containing the query:

---
queryMonth: 2022-11
---
Or
queryMonth:: 2022-11

And then change the query to something like:

```dataview
TABLE 
sum(map(rows.theDay, (r) => r[1])) as Created,       	
sum(map(rows.theDay, (r) => r[2])) as Closed 
FLATTEN dateformat(Created, "yyyy-MM-dd") as CreatedDay 
FLATTEN dateformat(Closed, "yyyy-MM-dd") as ClosedDay 
FLATTEN array([CreatedDay, 1, 0], [ClosedDay, 0, 1]) as theDay 
WHERE
  (contains(file.name, "INC") OR (contains(file.name, "SCTASK"))) 
  AND !contains(Created, null) 
  AND !contains(Closed, null) 

GROUP BY theDay[0] as Day
WHERE dateformat(Day, "yyyy-MM") = dateformat(queryMonth, "yyyy-MM")
```

This (untested) query should limit the result to be related to the queryMonth for either the created or closed day.

Update: Showed how the queryMonth needs to be formatted if placed in frontmatter or body text.

That will still give me the same issue because if a ticket is created on a month and closed in the next where will it be counted? Is it not possible to instead of using a field in the notes just tell Dataview to get the actions that happened in March (be it created or closed)?

Isn’t that what this does? It should query for either the creation or closing to be in the given month?

It’ll be counted as created in the month it was created, and closed in the month it was closed. Wasn’t that what you asked for, or am I totally misunderstanding you?

I’m pretty sure I’m the one not understanding.

So using that method what month do I put in the querymonth on the note? The created or closed? Or is it irrelevant and it’s just to be used in grouping?

The queryMonth is the one you’re wanting to see the result for. That is given a queryMonth of March 2023 ( aka 2023-02), it should show tickets created or closed in March 2023.

If it doesn’t, the query is somewhat wrong. :slight_smile:

Finally got the time to test your query and I’m getting the error

Dataview: Every row during operation 'where' failed with an error; first 3:

                - No implementation of 'dateformat' found for arguments: string, string
- No implementation of 'dateformat' found for arguments: string, string
- No implementation of 'dateformat' found for arguments: string, string

See my updated version above, if you’re using queryMonth in the frontmatter:

---
queryMonth: 2023-03
---

and if in the body text:

queryMonth:: 2023-03

So that was an error on my behalf, but how did you get it to be a string? What did you write in your note with the query, and the exact query?

OK, when I took a closer look at your modified query you kind of got the gist of it, but it was a point in the other query to actually make theMonth into a string for comparison purposes.

When you want to look at the days separately, it’s not that much of a point, so it could most likely be simplified a little:

```dataview
TABLE 
sum(map(rows.theDay, (r) => r[1])) as Created,       	
sum(map(rows.theDay, (r) => r[2])) as Closed 
FLATTEN array([Created, 1, 0], [Closed, 0, 1]) as theDay 
WHERE
  (contains(file.name, "INC") OR (contains(file.name, "SCTASK"))) 
  AND !contains(Created, null) 
  AND !contains(Closed, null) 

GROUP BY theDay[0] as Day
WHERE dateformat(Day, "yyyy-MM") = dateformat(this.queryMonth, "yyyy-MM")
```

Also note the crucial difference of doing this.queryMonth instead of just queryMonth, to make it only read that value from the current file containing the query.

Alternatively, you could modify your query variant with a WHERE clause of:

WHERE dateformat(date(Day), "yyyy-MM") = dateformat(this.queryMonth, "yyyy-MM") 

This would both remove the error messages, since it’s converting the Day which is a string back into a date, and compare it against the this.queryMonth instead of a (non-existent) `queryMonth in every file.

I am using in the frontmatter. I just copied and pasted your code. Didn’t change anything.