Accumulate the values of property hours:: from the journal of a specific month

Questions & Help Queries
1 
#+BEGIN_QUERY
{
  :title "Total Hours for [Your Month Here]"
  :inputs ["2024/11"] ;; Replace with "YYYY/MM" for the target month
  :query [
    :find (sum ?h)
    :in $ ?month-prefix
    :where
      [?p :block/properties ?props]
      [(get ?props :hours) ?h]
      [?p :block/journal? true]
      [?p :block/journal-day ?d]
      ;; Convert journal day to a string and check if it contains the month-prefix
      [(str ?d) ?date-str]
      [(clojure.string/includes? ?date-str ?month-prefix)]
  ]
  :result-transform (fn [result] [(str "Total hours: " (or (first result) 0))])
  :table-view? false
}
#+END_QUERY

With the above query, I am trying to accumulate the values of property hours:: from the journal pages of a specific month, but it returns 0 instead of the sum of the integer values.

Would you like to help me?

2

Welcome.

  • :block/journal-day returns number 20241108
  • For getting string "2024/11/08", should instead use :block/name
  • UPDATE: Should also add a line :with ?p , otherwise duplicate values count only once.
1 Like
3

Sadly, the query doesn’t include in the result all the journal pages for November, only a few.

4

Cannot guess the issue, unless you share some of those pages.

5

Simply in 5 of the journal pages of November, I added a property hours:: 2 or hours:: 3

With this other query, I can catch all of them, but with the previous one, I cannot.

#+BEGIN_QUERY
{
  :title "Debug Journal Entries for [Your Month Here]"
  :inputs ["2024/11"] ;; Replace with "YYYY/MM" for the target month
  :query [
    :find (pull ?p [:block/properties :block/journal-day])
    :in $ ?month-prefix
    :where
      [?p :block/properties ?props]
      [?p :block/journal? true]
      [?p :block/name ?name]
      [(str ?name) ?date-str]
      [(clojure.string/includes? ?date-str ?month-prefix)]
  ]
  :result-transform (fn [result] result)
  :table-view? true
}
#+END_QUERY
6

Try opening the respective markdown files in an external editor to check if property hours:: applies to the first block instead of the page itself.

7

It appears to be on the first row of each file, followed by an empty row and then start the blocks preceded by a - sign

8

That’s the correct place for pages. But are they all there? Is there any other difference between the markdown of the working pages and that of the non-working ones? Maybe you could share a screenshot in case we spot any clue. Try also recreating the issue in a new empty graph and compare which days work and which don’t.

9

I have the hours:: property in 6 journal entries. The total number of November properties is 14.

The series is the following
4
2
2
1
2
3

I observe that the first query sums only the original values, not the duplicate ones. For example, there are three occurrences of the number 2, but it is added only once.

10

To include duplicate values, add a line :with ?p somewhere after :find and before :where

1 Like
11

Perfect! Thank you very much!

12 
#+BEGIN_QUERY
{
  :title "Total Hours for November 2024"
  :inputs [ "2024/11" ] ;; Replace with "YYYY/MM" for the target month
  :query [
    :find (sum ?h)
    :in $ ?month-prefix
    :with ?p
    :where
      [?p :block/properties ?props]
      [(get ?props :hours) ?h]
      [?p :block/journal? true]
      [?p :block/name ?name]  ;; Returns "2024/11/08"
      ;; [?p :block/journal-day ?d]  ;; Returns number 20241108
      ;; Convert journal day to a string and check if it contains the month-prefix
      [(str ?name) ?date-str]
      [(clojure.string/includes? ?date-str ?month-prefix)]
  ]
  :result-transform (fn [result] [(str "Total hours: " (or (first result) 0))])
  :table-view? false
}
#+END_QUERY

This is the final version I share for anybody who can use or modify it.

2 Likes