Long time lurker, but now i finally made an account because I cant figure it out :\
What I’m trying to do
Im trying to calculate the total amount of jogging I did.
I created a dataview query, but im getting no results.
My key: value is "amount-run:: “1500” and they reside in my daily notes.
Oher queries do recognize the key:value, but when I try to get a total it just says “no results”
Im trying to calculate the total amount of jogging I did.
I created a dataview query, but im getting no results.
My key: value is "amount-run:: “1500” and they reside in my daily notes.
Oher queries do recognize the key:value, but when I try to get a total it just says “no results”
Things I have tried
my query:
TABLE WITHOUT ID sum(rows.amount-run) as “Total Amount Run”, length(rows.amount-run) as “Count”, sum(rows.amount-run)/ length(rows.amount-run) as “Average Amount Run”, min(rows.Date) as “First Entry”, max(rows.Date) as “Last Entry” FROM “Notes/01 Daily” GROUP BY dateformat(date, “yyyy-MM”) as “Month-Year” WHERE amount-run
```dataview
TABLE WITHOUT ID
sum(rows.amount-run) as "Total Amount Run",
length(rows.amount-run) as "Count",
sum(rows.amount-run)/ length(rows.amount-run) as "Average Amount Run",
min(rows.Date) as "First Entry",
max(rows.Date) as "Last Entry"
FROM "Notes/01 Daily"
GROUP BY dateformat(date, "yyyy-MM") as "Month-Year"
WHERE amount-run
```
This is just a reformatted version of your query, but I believe if you switch the last two rows you might get the output you want. After you’ve done the GROUP BY there is no amount-run available, since it’s then located within rows.amount-run.
In addition you could use FLATTEN to calculate some of the stuff just once. So with these changes you end up with:
```dataview
TABLE WITHOUT ID
totalRun as "Total Amount Run",
count as "Count",
totalRun/count as "Average Amount Run",
min(rows.Date) as "First Entry",
max(rows.Date) as "Last Entry"
FROM "Notes/01 Daily"
WHERE amount-run
GROUP BY dateformat(date, "yyyy-MM") as "Month-Year"
FLATTEN sum(rows.amount-run) as totalRun
FLATTEN length(rows) as count
```
Bonus tip: How to present code properly in a forum post
If you want to showcase either markdown, or code blocks, or dataview queries properly in a forum post, be sure to add one line before and one life after what you want to present with four backticks, ````. This will ensure that any other backticks (like in code blocks or queries) is properly shown.
Thank you so much for your reply.
The queries work, but I am still getting no results .
I tried troubleshooting by using frontmatter as amount-run, editing my values, reload and more.
The key itself is recognized by other dataview queries, so the problem is not there. my Dateformat in dataview is in line with your query.
I suppose ill start taking the query apart and troubleshoot line by line first (after work), but if you have any suggestions Im open to them
Yes, its “yyyy-MM-dd”
My daily notes have the same date properties (in the periodic notes plugin), except it has folder instructions > “YYYY/MM/YYYY-MM-DD”
My daily note template has the following frontmatter:
created-date: <% tp.file.title %>
summary:
trained-today:
migraine:
The values and key is part of the note like so:
amount-run:: “0000”
I cant for the life of me not figure out what i’m doing wrong!
Your suggestions are perfect, but i’m just not getting any data returns.
Perhaps you see something in the date formats i’m missing?
Does it work without the quotes around the inline value?
amount-run:: 1500
```dataview
TABLE WITHOUT ID
totalRun as "Total Amount Run",
count as "Count",
totalRun/count as "Average Amount Run",
min(rows.Date) as "First Entry",
max(rows.Date) as "Last Entry"
FROM ""
WHERE amount-run
GROUP BY dateformat(date, "yyyy-MM") as "Month-Year"
FLATTEN sum(rows.amount-run) as totalRun
FLATTEN length(rows) as count
```
This will most likely be consider strings/texr, and not number, so you’ll need to address this. Either change to a pure number notation (aka remove the quotes), or find some variant using number() to convert into numbers before adding them together.
You could try something like the following untested query:
```dataview
TABLE WITHOUT ID
totalRun as "Total Amount Run",
count as "Count",
totalRun/count as "Average Amount Run",
min(rows.Date) as "First Entry",
max(rows.Date) as "Last Entry"
FROM "Notes/01 Daily"
WHERE amount-run
FLATTEN number(amount-run) as amount
GROUP BY dateformat(date, "yyyy-MM") as "Month-Year"
FLATTEN sum(rows.amount) as totalRun
FLATTEN length(rows) as count
```
Note the addition of the `FLATTEN` line before the grouping to convert the `amount-run` text field, to the `amount` number field which are used in the summation later on.
