Troubleshooting a Dataview field

Hi guys, I’m using Dataview to categorize my journaling notes by week, month, year, etc. I have weekly notes that I want to round up in Dataview with the field month. The weekly note template that I use autofills the month for me (thanks to Templater) with the following line of code: <% tp.date.now("YYYY-MM") %>

The problem is that Dataview is not picking up the correct months at all. When I use the following code to pull up what Dataview is reading in the month field:

LIST
WHERE month
FLATTEN month
GROUP BY month

It only sees:

  • 2021-00-01
  • 2022-00-01
  • <% tp.date.now(“YYYY-MM”) %>

I am expecting Dataview to return keys like 2022-07, I find it very odd that Dataview is picking up a month value of basically zero.

Things I have tried

In the weekly journaling notes, I’ve tried surrounding the month key in quotations and brackets. I’ve double-checked that there is exactly one space separating the colon (from the field name) and the key itself.

Topic

Summary

Date vs. dateformat


Input

Summary

dictionary files

location: “100_Project/02_dataview/Q96_month/Q96_test_data”

A04

  • filename : dic_20140401
---
month: "2014-04"
cash: 400
---

  • filename : dic_20140406
---
month: "2014-04"
cash: 800
---

F02

  • filename : dic_20150201
---
month: "2015-02"
cash: 200
---

  • filename : dic_20150206
---
month: "2015-02"
cash: 600
---

R03

  • filename : dic_20150301
---
month: "2015-03"
cash: 300
---

  • filename : dic_20150306
---
month: "2015-03"
cash: 700
---

Y05

  • filename : dic_20140501
---
month: "2014-05"
cash: 500
---

  • filename : dic_20140506
---
month: "2014-05"
cash: 900
---


DQL10_debug_TABLE_month_of_estheon

Summary

2.1. Main DQL

Code Name Data type Group By Purposes Remark
DQL10_debug_TABLE_month
_of_estheon
month:a date format no 1.To debug: month step by step
2.To fix the bug: use dateformat to convert month(Type: date) to string

Code DQL10_debug_TABLE_month_of_estheon

Summary
title: DQL10_debug_TABLE_month_of_estheon =>1.To debug: `month` step by step 2.To fix the bug: use `dateformat` to convert `month`(Type: date) to string
collapse: close
icon: 
color: 
```dataview
TABLE WITHOUT ID 
      file.link AS "File",
      cash AS "Cash",
      month AS "S1_Month",
      typeof(month) AS "S2_T_Month",
      F_year_month AS "S3_year_month",
      F_year_month2 AS "S4_year_month2"
FROM "100_Project/02_dataview/Q96_month/Q96_test_data"
WHERE month
FLATTEN dateformat(month, "yyyy-MM") AS F_year_month
FLATTEN dateformat(file.day, "yyyy-MM") AS F_year_month2
SORT file.name ASC
```

Screenshots(DQL10)


DQL20_TABLE_FLATTEN_groupBy

Summary

Main DQL

Code Name Data type Group By Purposes Remark
DQL20_TABLE_FLATTEN_groupBy month:a date format yes 1.To sum up cash
2.WHERE month
3.groupBy GF_year_month

Code DQL20_TABLE_FLATTEN_groupBy

Summary
title: DQL20_TABLE_FLATTEN_groupBy =>1.To sum up cash 2.WHERE month 3.groupBy GF_year_month
collapse: close
icon: 
color: 
```dataview
TABLE WITHOUT ID
      rows.file.link AS "File",
      rows.cash AS "Cash",
      sum(rows.cash) AS "sum_cash",
      GF_year_month AS "year_month"
FROM "100_Project/02_dataview/Q96_month/Q96_test_data"
WHERE month
FLATTEN dateformat(file.day, "yyyy-MM") AS F_year_month
GROUP BY F_year_month AS GF_year_month
SORT GF_year_month ASC
```

Screenshots(DQL20)


2 Likes

Hi justdoitcc,

Thank you for your response! I was able to try your debugging code on my end. Here is how I paraphrased it:

TABLE WITHOUT ID
	file.link AS "File",
	month AS "S1_Month",
	typeof(month) AS "S2_T_Month",
	F_year_month AS "S3_year_month", 
	F_year_month2 AS "S4_year_month2" 
WHERE month 
FLATTEN dateformat(month, "yyyy-MM") AS F_year_month 
FLATTEN dateformat(file.day, "yyyy-MM") 
AS F_year_month2 SORT file.name ASC

Unfortunately, I didn’t get quite the same result as you. Specifically, the dateformat(file.day, "yyyy-MM") returned null output. Do you know what might be going on? I’m using Dataview 0.5.41 and Obsidian 0.15.8.

Actually, I have a different, more specific concern than trying to replicate your results now.

I have this code that selects weekly journaling notes for the month of July 2022:

TABLE
WHERE dateformat(month, "yyyy-MM") = "2022-07"

My new problem is that this code will not select weekly journaling notes that have two values for the month key.

For example, I wrote [2022-07, 2022-08] for the value of the month key in the weekly note for Week 32 since it straddles across two months. The code above does not include this weekly note. (Furthermore, when I surround 2022-07 in brackets in weekly notes that are only part of July 2022, this Dataview code doesn’t select it. So I think Dataview is viewing it as an array that dateformat cannot handle.) What code would also select the note for such note?

1 Like

file.day

Summary

1.My test note such as “dic_20140401” which file.name contains “yyyymmdd” has file.day.

2.Any daily note such as “2014-04-01” which file.name contains “yyyy-mm-dd” has file.day.

3.Your weekly note such as “2021-W22” has not file.day. As a result, it is OK that the dateformat(file.day, “yyyy-MM”) returned null output.

4.The code dateformat(file.day, "yyyy-MM") is copied and modified from the following:
Solutions


Reference

Summary

If the file has a date inside its title (of form yyyy-mm-dd or yyyymmdd), or has a Date field/inline field, it also has the following attributes:

file.day: An explicit date associated with the file.

Topic

Summary

0.Learn the typeof function to debug a code.

  • The following DQL must contain the typeof function for you in order to gain a deeper understanding of the function.
    1.How to distinguish lists from the other data types?
    2.How to filter by a list and compare by string?
    3.How to filter by a list and compare by date?

typeOf : released at 2022-07-08 from Dataview_v0.5.30

  • v0.5.30 Added the typeof(any) function in Dataview, which obtains the type of any value for comparison
export namespace DefaultFunctions {
    export const typeOf = new FunctionBuilder("type")
        .add1("array", _ => "array")
        .add1("boolean", _ => "boolean")
        .add1("date", _ => "date")
        .add1("duration", _ => "duration")
        .add1("function", _ => "function")
        .add1("html", _ => "html")
        .add1("link", _ => "link")
        .add1("null", _ => "null")
        .add1("number", _ => "number")
        .add1("object", _ => "object")
        .add1("string", _ => "string")
        .add1("*", _ => "unknown")
        .build();

Input

Summary

dictionary files

location: "100_Project/02_dataview/Q96_month/20220802_month_02/Q96_test_data_02

R03

  • filename : dic_20120301
---
month: ["2012-03", "2012-04"]
---
  • filename : dic_20120306
---
month: "2012-03"
---

A04

  • filename : dic_20120401
---
month: "2012-04"
---
  • filename : dic_20120406
---
month: "2012-04"
---

DQL10_distinguish_lists_from_the_other_data_types

Summary

Main DQL

Code Name Data type Group By Purposes Remark
DQL10_distinguish_lists_from
_the_other_data_types
month: date or a list of dates no 1.Use typeof to distinguish lists from the other data types

2.The following DQL must contain the typeof function for you in order to gain a deeper understanding of the function.

Code DQL10_distinguish_lists_from_the_other_data_types

Summary
title: DQL10_distinguish_lists_from_the_other_data_types => 1.Use `typeof` to distinguish lists from the other data types 2.The following DQL must contain the `typeof` function for you in order to gain a deeper understanding of the function.
collapse: close
icon: 
color: 
```dataview
TABLE WITHOUT ID
      file.link AS "File",
      dateformat(month, "yyyy-MM") AS "month",
      typeof(month) AS "T_month"
FROM "100_Project/02_dataview/Q96_month/20220802_month_02/Q96_test_data_02"
WHERE month

```

Screenshots(DQL10)


DQL20_deal_with_lists_only_and_compare_by_string

Summary

Main DQL

Code Name Data type Group By Purposes Remark
DQL20_deal_with_lists_only
_and_compare_by_string
month: date or a list of dates no 1.Use typeof to filter by lists

2.Compare by string

3.The following DQL must contain the typeof function for you in order to gain a deeper understanding of the function.

Code DQL20_deal_with_lists_only_and_compare_by_string

Summary
title: DQL20_deal_with_lists_only_and_compare_by_string => 1.Use `typeof` to filter by lists 2.Compare by string 3.The following DQL must contain the `typeof` function for you in order to gain a deeper understanding of the function.
collapse: close
icon: 
color: 
```dataview
TABLE WITHOUT ID
      file.link AS "File",
      dateformat(month, "yyyy-MM") AS "month"
FROM "100_Project/02_dataview/Q96_month/20220802_month_02/Q96_test_data_02"
WHERE month
WHERE typeof(month) = "array"
WHERE contains(dateformat(month, "yyyy-MM"), "2012-03")
WHERE contains(dateformat(month, "yyyy-MM"), "2012-04")
```

Screenshots(DQL20)


DQL30_deal_with_lists_only_and_compare_by_date

Summary

Main DQL

Code Name Data type Group By Purposes Remark
DQL30_deal_with_lists_only
_and_compare_by_date
month: date or a list of dates no 1.Use typeof to filter by lists

2.Compare by date

3.The following DQL must contain the typeof function for you in order to gain a deeper understanding of the function.

Code DQL30_deal_with_lists_only_and_compare_by_date

Summary
title: DQL30_deal_with_lists_only_and_compare_by_date => 1.Use `typeof` to filter by lists 2.Compare by date 3.The following DQL must contain the `typeof` function for you in order to gain a deeper understanding of the function.
collapse: close
icon: 
color: 
```dataview
TABLE WITHOUT ID
      file.link AS "File",
      dateformat(month, "yyyy-MM") AS "month"
FROM "100_Project/02_dataview/Q96_month/20220802_month_02/Q96_test_data_02"
WHERE month
WHERE typeof(month) = "array"
WHERE contains(month, date("2012-03"))
WHERE contains(month, date("2012-04"))
```

Screenshots(DQL30)


Excellent investigating here! Just to add on to what @justdoitcc has written, if you are trying to find something in a list, equality does not work, since the list does not equal one element of that list! Instead contains(list, element) will test whether list contains element. Since your output is strings, and strings are lists of characters internally, contains(dateformat(month...), "2022-07") should work regardless of whether month is a list! If month is not a list, well, “2022-07” does contain “2022-07”!

1 Like