Dataview Sums and Average

That does feel like improvement. :slight_smile: At least the query isn’t erroring out now.

Would you be willing to post an example of what a page of your source data looks like? That might help us track down what’s going on here.

1 Like

Sure, this is the template of my daily note
image

The highlight is temporary, meaning that I erase it as I input the right data at the end of the day.

Hmm, I thought perhaps the highlight might be the issue, but that gives a different error. I’m not sure what we’re doing differently that it works for me but not for you. :frowning:

1 Like

Sorry to interfere, but there is most likely one or more of the minutes which hold some strange value, so maybe try this query and look for anomalies.

```dataview
TABLE WITHOUT ID min(rows.minutes)
FROM "Archive 🗃️/Journal 📅/Days"
WHERE MONTH = [[2023-Jan]]
WHERE minutes
Group by minutes
```
1 Like

no interference here :grinning:

actually I had some entries like “-” changed them to 0 and actually the query provides a different error message

image

To provide more context, everything work smooth when I query the individual dates without making any sum or average

So now it seems like the sum() function to generate the Average generates the error.

I got another trick up my sleeves to detect the anomaly in your data set. Could you please try the following:

```dataviewjs
const result = await dv.query(`
TABLE minutes
FROM "Archive 🗃️/Journal 📅/Days"
WHERE MONTH = [[2023-Jan]]
WHERE minutes
`)

if ( result.successful ) {
   const noNumbers = dv.array(result.value.values)
    .where( v => typeof(v[1]) != "number")
    .map( v => [ v[0], v[1], typeof(v[1]) ] )

   dv.table(["File", "field", "type"], noNumbers)
} 
else
  dv.paragraph("~~~~\n" + result.error + "\n~~~~")
```

With a contrived test set of mine, I got the following output:

Where all my noNumbers were:

  • the first entry was a minutes:: "660", aka string
  • the second entry was multiple minutes:: definitions
  • the third had the value ==660==, aka highlighted
  • the fourth was a random link?!

What do you get running that query on your data set?

1 Like

Please excuse me if I make any stupid mistakes because I have no knowledge of the dataviewjs language.

I tried the query on both folders, (A) containing missing and “-” values for “minutes” and (B) containing only actual values ie. numbers or 0

This is the result I’ve got for A

image

and for B
image

all this dates contain 0 as a value, ie:

image

Is this helpful?

It showcases whether you’ve got any faulty data. So you should be able to run the main script on “A” since that didn’t find any non numbers. What was the result whennrunning the main script on “A”?

On “B” however, you need to go through every file in the list and change those entries having the “*” as value for minutes, as thoseb files will trip up the main script. After cleaning up (and getting a “no result” there as well), then what do you get when doing the main script for “B”?

1 Like

Good catch! I agree with @holroy , it looks like you have some pages where the minutes value isn’t a number, and that could be what’s causing the script to fail.

1 Like

thank you @holroy and @Craig appreciate your efforts :pray:

image

I now have a new issue to solve :sweat_smile: namely, changing all "-"s to zero and all the previous labels that I used for Minutes’ for more than 2 years of daily notes

Do you have any ideas on how I could complete that task without doing it manually, perhaps using a Python script?

1 Like

This is kind of the culprit, whenever it’s not a number, which the previous queries kind of has led up to. Regarding how to handle it, you’ve got basically two options:

  1. Fix all the erroneous minutes (in over 2 years of files worth)
  2. Eliminate/igore all the erroneous minutes from the query

Alternative 1 is of course the political correct way of doing it, but it’s a hassle, to say the least.

Alternative 2 is far easier, but it comes at the cost of potentially hiding some rows, which you kind of would like to include. But given the other queries, let’s go for this version, and it is actually simpler than you’d think it be, as we just need to add typeof(minutes) = "minutes" to the where clause.

So here is the full query, where it simply ignores all the erroneous minutes values:

```dataview
TABLE WITHOUT ID Count, Tot, Avg, Min, Max
FROM "Archive 🗃️/Journal 📅/Days"
WHERE MONTH = [[2023-Jan]]
WHERE minutes AND typeof(minutes) = "number"
GROUP BY ""
FLATTEN length(rows.minutes) as Count 
FLATTEN sum(rows.minutes) as Tot
FLATTEN Tot / Count as Avg
FLATTEN min(rows.minutes) as Min
FLATTEN max(rows.minutes) as Max
```

Hope this helps, and that the process to get here has been educational.

3 Likes

I hadn’t thought of verifying the type of the field. Good thinking! :clap:

3 Likes

I’ll definitely go for Option 2, since I’ve no need to check summary statistics for previous years

It was really beneficial to see the process and how it all worked out, it was also a good reminder of the importance of learning dataviewJS.

As JavaScript is such a huge area of study, can you recommend some good resources for me to learn from? I’m not looking to learn the entire language, just enough to be able to write more powerful dataview queries.

Any advice would be greatly appreciated.
Thank you!

Well, you could take a masters degree in Computer Science, and work with it for a few decades, and stumble across javascript every now and then, and see where that takes you… :grinning:

Or you could just keep playing around with it, copying code which looks useful, and start tweaking it to your needs. Not sure on which are the better resources for learning javascript today, but there are loads of dataviewjs queries floating around in this forum, which maybe could help you learn more of it.

1 Like

Darn I really willl have too juggle with it, same way I did with dataview, which isn’t the most effective learning process but…

As someone who loves class-based languages, this makes me sad :sweat_smile:

It’ll take work, but then again if you ask questions which show what you’ve achieved so far and trying to achieve, and as such indicate that you’ve really tried to get it working, I think you’ll experience that those more familiar with the language will easier tend to help and guide you in the correct direction.

Nothing kills my spirit and helpfulness more than someone just saying: “Hey, can you do this for me?” or “This auto-generated ChatGPT code doesn’t work, how to fix?”. When I see a proper attempt from someone trying to get something done, I’ll more often step in to try and help.

2 Likes

Just as a point of clarification (and please forgive me if you already knew this), Dataview and DataviewJS are two very different but related things.

Everything we’ve discussed in this thread has been about the Dataview query language, which is an SQL-like language specifically for running Dataview queries. It’s (arguably) easier to use and more focused than DataviewJS, and is a great place for people to start learning about structured data in Obsidian.

DataviewJS is an environment for running plain JavaScript in your pages, with access to the Dataview libraries so you can do things like query, filter, etc. It’s more powerful than Dataview, but also far more complex and more time-consuming to learn. It’s a better fit for folks who already know Javascript and want to take advantage of the Dataview libraries.

Having said that, if you’re interested in learning either or both, go for it! But I’d recommend getting familiar with the Dataview query language first before diving into DataviewJS.

1 Like

This is indeed very useful @Craig, I was assuming that I need to learn dataviewJS in order to take full advantage of dataview.

Surely was feeling a bit intimidated by all the amazing things people can do with dataviewJS, but it’s great to know that I can still take advantage of dataview without the need to learnd dataviewJS.

I think I’ll focus on the functions where I’m still lacking.

Going back to the queries, I have 2 new questions now.

:one:
We were summarizing one particular field from the front matter, which is not the only one I’m tracking in the daily note. Let’s say I’m tracking 5 fields, with the previous approach, I’d need to create 5 different tables.

Is there a way to group them within a table? I believe the group by function can help, but this has been really confusing for me to master

:two:
Facing a new challenge…
One thing that I track is wake_up and end_by time. I input these in the form of 6:00, 20:53
By using the previous dataviewjs query I figured out that Obsidian read such entries as string. Hence I believe I have to convert them into numbers and then summarize them.

By looking at the Dataview documentation I figured out the number() and split(string) function could help, but honestly no Idea how to integrate them in the query that summarize results

`

`

I’m also open to suggestions on different tools to manage this type of information, but for me nothing beat having everything centralized in one place and data priavacy :slightly_smiling_face:

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