Dataview plugin snippet showcase

Glad you got it working, Gaston!

Same here, same here. Thank goodness computers usually let us undo and retry. :slight_smile:

Is there any way to combine not the LIST/TABLE but the TASK query w/ regexreplace?

Context:
I am using the KANBAN plugin and utilize dataview to pull all those tasks into a list which I display in the sidebar to have them in view all the time. The problem is that dataview also shows the due date set by the Kanban plugin which I’d love to cut out by using regexreplace.

This is what my current dataview query code looks like:

TASK 
WHERE !completed
WHERE due
GROUP BY due
SORT due ASC

And this is the result:

Is there some way to fiddle sth like

regexreplace(text, "\{.*$", "")

in there?

1 Like

Nice question! I don’t know how to do it in plain Dataview query language, but it should be pretty straightforward in dataviewjs. I’m imagining a map over the rows.text field after you have done the group (and all the rest of your query) but before passing to dv.taskList to display.
You might want “Fällig:” as part of your regex query unless you’re sure there will never be { in your tasks for any other reason?

Feel free to start a new thread if you have questions getting the dataviewjs to work! Meanwhile maybe some expert in this thread has an answer in the plain dataview query language!

Thanks for pointing out the “Fällig”, I’ve since replaced it with the original @ as trigger which might also make it somewhat easier to filter out via regex. I am used to the regular dataview syntax but have zero experience w/ the js version therefore help would be highly appreciated. :slightly_smiling_face:

1 Like

What is correct formatting of Obsidian link [[Link]] when I want to use WHERE filter?

YAML


---

type: [[grammar]]
origin: english

---

Dataview query

LIST
WHERE type = [[grammar]]

With this syntax will got 0 result.

Here’s a milestone tracker I use. It creates a nice view for tracking your progress along some hobby or project.

You create a normal markdown list, for example tracking your milestones along
the journey of learning Javascript:

- 2022-02-06 Wrote a "Hello world" script, and it worked!
- 2022-02-11 Learned how to fetch data from a URL
- 2022-02-28 Learned about Promises
- 2022-03-03 Learned about await/async
- 2022-05-02 Started working with NPM libraries
- 2022-07-10 Published my first code to Github

```dataviewjs
const startDate = '2022-02-05'
dv.table(['Time', 'Event'],
  (await dv.io.load(dv.current().file.path))
    .split('\n').filter(x => x.startsWith('- '))
    .map(x => { const line = x.match(/- (\d{4}-\d{2}-\d{2}) (.+)$/)
      return [moment(line[1]).from(moment(startDate), true), line[2]] }))

And Dataview can render it to look like this, giving you a nice timeline of your milestones:

9 Likes

Neat! I wonder: Can you use the implicit file.lists field documented here to avoid having to read the file?

Here is my DVQ:

TABLE without ID Link.up As “Main Category”,(Link + “(” + length(rows.file.link) + “)”) AS “Category”,rows.file.link AS “Thought Note”, rows.file.cday AS “Date”
FROM #z/litnote and !“81 ADMIN”
Flatten Link
Group by Link
sort Link.up ASCENDING

And a screen shot of the output:

I can’t figure out:

  1. get a bullet point before each “Thought Note”, and
  2. get the date to line up more closely to the associated note name

Any suggestions?

Denise

2 Likes

Neat! I wonder: Can you use the implicit file.lists field documented here to avoid having to read the file?

Oh nice, that’s made it a bit simpler :+1: I’m always down to improve efficiency!

I also got rid of the startDate and just use the first bullet point for that now:

```dataviewjs
let t, s, r = dv.current().file.lists.values.filter(x => x.text.match(/^\d{4}-\d{2}-\d{2} .+/))
dv.table(['Time', 'Event'], r.map(x => {
    let [_, a, b] = x.text.match(/^(\S+)\s+(.+)$/); [t, s] = s ? [moment(a).from(moment(s), true), s] : [a, a]
    return [t, b]
}))```

- 2022-02-05 Started learning Javascript
- 2022-02-06 Wrote a "Hello world" script, and it worked!
- 2022-02-11 Learned how to fetch data from a URL
- 2022-02-28 Learned about Promises
- 2022-03-03 Learned about await/async
- 2022-05-02 Started working with NPM libraries
- 2022-07-10 Published my first code to Github

https://i.imgur.com/rpyJ1Uv.png

5 Likes

This should be so simple but I’m having a brain freeze.
I have a very simple DVQ that lists all open tasks:

Task from “”
WHERE !completed

That query is listed on my Daily Note Page. I want the query to NOT include any tasks listed on that page itself. My daily note page is title by date. Thus if I do this:

Task from “”
WHERE !completed and file.name !=“2022-02-17”

Then I get what I want. But I don’t want to “hard code” the date.

I tried using the date(today) type combination, but of course, that isn’t a string so it doesn’t work file.name. Also tried concatenating a string, but that didn’t work either. I can’t use file.cday because it would exclude other files created that day (not just the today’s daily note page, like I want).

Thoughts?
I feel like I’m missing something very simple and straightforward.

UPDATE: I GOT IT!
Task from “”
WHERE !completed and !contains(file.name,this.file.name)

Problem solved.

2 Likes

I have a tabel generated by dataviewjs.
I’m not sure if it has the same table style with dataview.

My issue is wth the above code, it is OK on the screen.
But, when I print out by “Export to pdf”, all of the contents including the header are disappeared.
The table borders are alive.

Obsidian : v0.15.6
Theme : Default

I don’t know whre I have to change to fix this. So, it would be great if someone give me a tip.

=======================

I didn’t change anything. But, It’s ok now. Maybe after restart???

2 Likes

Hi! I’d love some help for formatting.

I’ve started logging my meals in my daily notes.

I use this code:

TABLE typerepas as "Type de repas", heurerepas AS "Heure", faimavantrepas AS "Faim avant", repas AS "Repas", faimapresrepas AS "Faim après"
FROM "Daily notes"
WHERE typerepas != FALSE
LIMIT 15
SORT file.cday DESC

Currently it looks like this:

Is there a way to “clean it up” so that each bullet point has its own row, so that the columns are aligned for each meal instead of having the daily info and not being properly aligned?

Thanks so much!

You would have to flatten the lists: Queries - Dataview

1 Like

Can one flatten multiple lists at a time? I’m not sure dataview would know how the entries correlate.

Topic : S01

How to sum up a number, null, or a list of non-groupBy(or groupBy) data with skipping the null value? PS.To sum up one or two fields

Summary

0.field cash =>type: a number, null or a list
0.field card =>type: a number, null or a list
1.DQL10 : How to sum up one or two fields such as cash and card(type: a number, null, or a list) without using groupBy?
2.DQL20 : How to sum up one field such as cash with using groupBy step by step?
3.DQL30 : How to sum up one or two fields such as cash and card(type: a number, null, or a list) with using groupBy?
4.DVJS10: How to TABLE the last_row_Total with sum(cash) and sum(card)?
5.DQL40 : How to TABLE last_row_Total merged with using groupBy and SORT file.name ASC?
6.DQL50 : How to TABLE last_row_Total merged with using groupBy and SORT file.name ASC? (To fix Total:111739.79999999999)
7.DQL60 : How to TABLE last_row_Total merged with using groupBy and SORT file.name DESC?


1. Test

Summary
  • by dataview_v0.5.41

2. Overview

Summary

2.1. Main DQL or DVJS

  • The fowllowng table presents the main DQLs in the Solutions.
Code Name Data type Group By Purposes Remark
DQL10_sum_not_null a number, null, or a list no 1.To sum up one or two fields such as cash and card It is finished.
DQL20_debug_sum_cash_groupBy a number, null, or a list yes 1.To sum up one field such as cash
2.step by step
DQL30_sum_GROUP_BY_G_month a number, null, or a list yes 1.To sum up one or two fields such as cash and card It is finished.
DVJS10_sum_not_null_last_row 1.a number ,null, a list no 1.get i_Total_cash
2.get i_Total_card
3.get i_Total
4.To display them as a table
DQL40_sum_not_null_last_row_ASC a number, null, or a list no 1.To sum up one or two fields such as cash and card
2.To add Total in the last row
3.The unique zzz_2018_Year_Total file in the source folder includes the same DVIF fields, where the inline DVJS is.
4.Require SORT file.name ASC
1.The code is copied from DQL10_sum_not_null.
2.To add Total in the last row
3.SORT file.name ASC: To use zzz_2018_Year_Total
4.SORT file.name DESC:To use _____2018_Year_Total


5.The last row Total:111739.79999999999 is to be fixed in the next version
DQL50_sum_not_null_last_row_ASC a number, null, or a list no 1.To sum up one or two fields such as cash and card
2.To add Total in the last row
3.The unique zzz_2018_Year_Total file in the source folder includes the same DVIF fields, where the inline DVJS is.
4.Require SORT file.name ASC
5.To fix Total:111739.79999999999 which is from the expression 44703.1 + 67036.7
1.The code is copied from DQL40_sum_not_null_last_row_ASC.
2.To add Total in the last row
3.SORT file.name ASC: To use zzz_2018_Year_Total
4.SORT file.name DESC:To use _____2018_Year_Total


5.To fix Total:111739.79999999999
DQL60_sum_not_null_last_row_DESC a number, null, or a list no 1.To sum up one or two fields such as cash and card
2.To add Total in the last row
3.The unique ____2018_Year_Total file in the source folder includes the same DVIF fields, where the inline DVJS is.
4.Require SORT file.name DESC
5.To fix Total:111739.79999999999 which is from the expression 44703.1 + 67036.7
1.The code is copied from DQL50_sum_not_null_last_row_ASC.
2.To add Total in the last row
3.SORT file.name ASC: To use zzz_2018_Year_Total
4.SORT file.name DESC:To use _____2018_Year_Total


5.To fix Total:111739.79999999999
Summary_Examples

2.2. Examples

2.2.1. javascript

```javascript
let nResult = null + 10; //=>10
```

2.2.2. Inline DQL

ng_Result=`=null + 10` //=>No implementation found for 'null + number'
nResult= `=default(null, 0) + 10` //=>10

2.2.3. Summary

  • Use default function to transform null into zero within dataview code block


3. Input

Summary

3.1.1. Requirements

  • Suppose that there are many files located at the path 100_Project/02_dataview/Q01_Sum/Q01_test_data.
  • Suppose that the filenames including dic_ are to be taken into consideration.
  • To sum up a number, null, or a list of non-groupBy data with skipping the null value
> The `dic_20220702`, `dic_20220707`, `dic_20220802` and `dic_20220907` must be presented in the current note.
  • To sum up a number, null, or a list of groupBy data with skipping the null value
> The `dic_20220802` and `dic_20220907` must be presented in the current note.

3.2. report files

3.2.1. 00_report

  • filename : zzz_2018_Year_Total
> - The report file is ONLY used as the last row of a table by DQL40_sum_not_null_last_row_ASC and DQL50_sum_not_null_last_row_ASC.
> - The file is unique in an Obsidian vault.

---
cash: 44703.1
card: 67036.7
---

### zzz_2018_Year_Total
- The report file is ONLY used as the last row of a table by DQL40_sum_not_null_last_row_ASC and DQL50_sum_not_null_last_row_ASC.
- The file is unique in an Obsidian vault.



  • filename : ____2018_Year_Total
> - The report file is ONLY used as the last row of a table by DQL40_sum_not_null_last_row_ASC.
> - The file is unique in an Obsidian vault.

---
cash: 44703.1
card: 67036.7
---

### ____2018_Year_Total
- The report file is ONLY used as the last row of a table by DQL60_sum_not_null_last_row_DESC.
- The file is unique in an Obsidian vault.


3.3. dictionary files

  • The filenames in the Q01_test_data folder match the /^dic_20220[2-7]0[27]$/ or /^(dic_20220802)|(dic_20220907)$/ Regular Expression.
  • The file.mtime of each file is equal to file.ctime + dur(2 days).
  • For example, the file.ctime of the dic_20220302 is 2022-03-02T19:30:50. The file.mtime of the dic_20220302 is 2022-03-03T19:30:50.
  • The files are used primarily in testing and debugging with the sum function.

3.3.1. folder: 02_list

  • filename : dic_20220202_YAML
---
cash: [10, 12]
card: [15, 17]
---

  • filename : dic_20220207_DVIF
cash:: 20, 22
card:: 25, 27

3.3.2. folder: 03_null_or_undefined

  • filename : dic_20220302
cash:: 
card:: 

  • filename : dic_20220307
cash:: 


> dic_20220307 
> cash => null(dataview_v0.5.36);|    cash => undefined(dataview_v0.4.26) 
> card => undefined(dataview_v0.5.36)

3.3.3. folder: 04_number

  • filename : dic_20220402
cash:: 202
card:: 302

  • filename : dic_20220407
cash:: 207
card:: 307

3.3.4. folder: 05_number

  • filename : dic_20220502
cash:: 2002
card:: 3002

  • filename : dic_20220507
cash:: 2007
card:: 3007

3.3.5. folder: 06_number_or_list

  • filename : dic_20220602
cash:: 10001, 10001
card:: 30002

  • filename : dic_20220607
cash:: 20007
card:: 12000, 18007

3.3.6. folder: 07_list_or_null

  • filename : dic_20220702
cash:: 101, 101
card:: 

  • filename : dic_20220707
cash:: 
card:: 120, 187

3.3.7. folder: 08_number_or_null

  • filename : dic_20220802
cash:: 10.1
card::  

3.3.8. folder: 09_null_or_number

  • filename : dic_20220907
cash:: 
card:: 18.7

4. DQL10_sum_not_null:Final

Summary

4.1. Main DQL

Code Name Data type Group By Purposes Remark
DQL10_sum_not_null a number, null, or a list no 1.To sum up one or two fields such as cash and card It is finished.

4.2. Notes

Purpose:To sum up a number, null, or a list of non-groupBy data with skipping the null value

```ad-info
dic_20220702
cash:: 101, 101
card::
```
```ad-info
dic_20220707
cash::
card:: 120, 187
```
```ad-info
dic_20220802
cash:: 10.1
card:: 
```
```ad-info
dic_20220907
cash:: 
card:: 18.7
```

```ad-success
1.The `dic_20220702`, `dic_20220707`, `dic_20220802` and `dic_20220907` are presented in the current note.

Longer Codes:
- Left : `sum(cash)` ==>`choice(cash != null, sum(cash), 0)`
- Right: `sum(card)` ==>`choice(card != null, sum(card), 0)`
```

```ad-success
1.The `dic_20220702`, `dic_20220707`,`dic_20220802` and `dic_20220907` are presented in the current note.

Shorter Codes:
- Left : `sum(cash)` ==>`sum(default(cash, 0))`
- Right: `sum(card)` ==>`sum(default(card, 0))`
```

```ad-bug
1.The `dic_20220702`, `dic_20220707`, `dic_20220802` and `dic_20220907` are not presented in the current note where one DVIF field is null for us in order to sum up two fields such as cash and card by using `sum(cash) + sum(card)`.
2.The reason is that no implementation is found for 'null + number'.

alias: Left + Right AS "Total",
- `sum(cash) + sum(card)` AS "Total"
```

4.3. Code DQL10_sum_not_null

Summary_code
title: DQL10_sum_not_null => 1.To sum up one or two fields such as cash and card(type: a number, null, or a list) 2.non-groupBy data
collapse: open
icon: 
color: 
```dataview
TABLE WITHOUT ID      
      file.link AS "File",
      typeof(cash) AS "T_Cash",
      cash AS "Cash",
      card AS "Card",
      sum(default(cash, 0)) as "sum_cash",
      sum(default(card, 0)) as "sum_card",
      sum(default(cash, 0)) + sum(default(card, 0)) AS "Total"
FROM "100_Project/02_dataview/Q01_Sum/Q01_test_data"
WHERE contains(file.name, "dic_")
WHERE file.name != "zzz_2018_Year_Total"
WHERE file.name != "____2018_Year_Total"
WHERE file.name != this.file.name
WHERE cash != null OR card != null
SORT file.name ASC
```

4.3.1. Screenshots(DQL10)


5. DQL20_debug_sum_cash_groupBy

Summary

5.1. Main DQLs

Code Name Data type Group By Purposes Remark
DQL20_debug_sum_cash_groupBy a number, null, or a list yes 1.To sum up one field such as cash
2.step by step

5.2. Examples

a_Result11=`=map([10, 20, 30], (e) => e + 6)` //=>16, 26, 36

a_Result11=`=map(list(list(1, 2, 3)), (e) => sum(e))` //=>6

a_Result01=`=filter([10,20,30], (e) => e >= 16)`
//=>20, 30

5.3. Notes

Purpose:To sum up a number, null, or a list of groupBy data with skipping the null value

```ad-info
dic_20220702
cash:: 101, 101
card::
```

```ad-info
dic_20220707
cash::
card:: 120, 187
```

```ad-success
1.dic_20220702 and dic_20220707 are presented in the current note where one field is null and the other is a list. 

- 
`
sum(
    map(rows.cash, (e) =>
        sum(default(e, 0))
    )
)
` AS "sum_cash"
```


```ad-bug
1.dic_20220702 and dic_20220707 are not presented in the current note where one field is null and the other is a list.
2.The reason is that no implementation is found for 'null + number'.

- `sum(map(rows.cash, (e) => sum(e)))` AS "sum_cash",
```

5.4. Code DQL20_debug_sum_cash_groupBy

Summary_code
title: DQL20_debug_sum_cash_groupBy =>1.To sum up one field such as cash(type: a number, null, or a list) 2.groupBy data 3.step by step
collapse: open
icon: 
color: 
```dataview
TABLE WITHOUT ID
      G_m AS "G_m",
      typeof(rows.cash) AS "T_r_cash",
      map(rows.cash, (e) =>  sum(default(e, 0))) AS "S1_map_r_cash",
      sum(map(rows.cash, (e) =>  sum(default(e, 0)))) AS "S2_sum_r_cash",
      
      rows.cash AS "Cash_item"
FROM "100_Project/02_dataview/Q01_Sum/Q01_test_data"
WHERE contains(file.name, "dic_")
WHERE file.name != "zzz_2018_Year_Total"
WHERE file.name != "____2018_Year_Total"
WHERE file.name != this.file.name

SORT file.name ASC
GROUP BY file.day.month AS G_m

```

5.4.1. Screenshots(DQL20)


6. DQL30_sum_GROUP_BY_G_month :Final

Summary

6.1. Main DQL

Code Name Data type Group By Purposes Remark
DQL30_sum_GROUP_BY_G_month a number, null, or a list yes 1.To sum up one or two fields such as cash and card It is finished.

6.2. Notes: Longer codes as a reference

Purpose:To sum up a number, null, or a list of groupBy data with skipping the null value

```ad-info
dic_20220802
cash:: 10.1
card:: 
```
```ad-info
dic_20220907
cash:: 
card:: 18.7
```

```ad-success
1.dic_20220802 and dic_20220907 are presented in the current note where one field is null and the other is a number.  

Longer Codes:
- Left : `Left`  ==>`default(Left, 0)`
- Right: `Right` ==>`default(Right, 0)`
```


```ad-bug
1.dic_20220802 and dic_20220907 are not presented in the current note where one field is null and the other is a number.  
2.The reason is that no implementation is found for 'null + number'.

Longer Codes:
alias: Left + Right AS "Total",
sum(map(filter(rows.cash, (e) => e != null), (m) => sum(m))) + sum(map(filter(rows.card, (e) => e != null), (m) => sum(m))) AS "Total",
```

6.3. Code DQL30_sum_GROUP_BY_G_month

Summary_code
title: DQL30_sum_GROUP_BY_G_month =>1.To sum up one or two fields such as cash and card(type: a number, null, or a list) 2.groupBy data
collapse: open
icon: 
color: 
```dataview
TABLE WITHOUT ID
      G_month AS "G_month",
      sum(map(rows.cash, (e) =>  sum(default(e, 0)))) AS "sum_cash",
      sum(map(rows.card, (e) =>  sum(default(e, 0)))) AS "sum_card",
      
      sum(map(rows.cash, (e) =>  sum(default(e, 0)))) +
      sum(map(rows.card, (e) =>  sum(default(e, 0)))) AS "Total",

      rows.cash AS "Cash_item",
      rows.card AS "Card_item"
      
FROM "100_Project/02_dataview/Q01_Sum/Q01_test_data"
WHERE contains(file.name, "dic_")
WHERE file.name != "zzz_2018_Year_Total"
WHERE file.name != "____2018_Year_Total"
WHERE file.name != this.file.name
WHERE cash != null OR card != null
SORT file.name ASC
GROUP BY file.day.month AS G_month

```

6.3.1. Screenshots(DQL30)


7. DVJS10_sum_not_null_last_row

Summary

7.1. Main DVJS

Code Name Data type Group By Purposes Remark
DVJS10_sum_not_null_last_row 1.a number ,null, a list no 1.get i_Total_cash
2.get i_Total_card
3.get i_Total
4.To display them as a table

7.2. Code DVJS10_sum_not_null_last_row

Summary_code
title: DVJS10_sum_not_null_last_row =>1.get i_Total_cash 2.get i_Total_card 3.get i_Total 4.To display them as a table
collapse: close
icon: 
color: 
```dataviewjs
// M11. define pages: gather all relevant pages 
// #####################################################################
let pages = dv
    .pages('"100_Project/02_dataview/Q01_Sum/Q01_test_data"')
    .where((page) => dv.func.contains(page.file.name, "dic_"))
    .where((page) => page.file.name !== "zzz_2018_Year_Total")
    .where((page) => page.file.name !== "____2018_Year_Total")
    .where((page) => page.cash !== null || page.card !== null);
    

// M21. get i_Total_cash: dv.func.sum(dv.func.default(pages.cash, 0))
// #####################################################################
let i_Total_cash = dv.func.default(dv.func.sum(pages.cash), 0);


// M23. get i_Total_card: dv.func.sum(dv.func.default(pages.card, 0))
// #####################################################################
let i_Total_card = dv.func.default(dv.func.sum(pages.card), 0);


// M25. get i_Total: i_Total_cash + i_Total_card
// #####################################################################
let i_Total = i_Total_cash + i_Total_card;
i_Total = dv.func.round(i_Total, 1);


// M81. Output :i_Total_cash, i_Total_card, i_Total
// #####################################################################
// dv.span("i_Total_cash=" + i_Total_cash);//=>44703.1
// dv.span("<br>");
// dv.span("i_Total_card=" + i_Total_card);//=>67036.7
// dv.span("<br>");
// dv.span("i_Total=" + i_Total);//=>111739.8


// M91. TABLE :i_Total_cash, i_Total_card, i_Total
// #####################################################################
// dv.table(
//     ["Name", "Price", "Caffeine Content"],
//     [
//         ["Black Coffee", 120, 300],
//         ["Green Tea", 100, 200],
//         ["Apple Juice", 110, 0],
//         ["Iced Chocolate", 130, 0],
//         ["Hot Chocolate", 105, 6],
//     ]
// );

dv.table(
    ["", "cash", "card", "Total"],
    [
        ["**Total**", i_Total_cash, i_Total_card, i_Total],
    ]
);


```

7.2.1. Screenshots(DVJS10)

20220724_DVJS10_output_a01


8. DQL40_sum_not_null_last_row_ASC

Summary

8.1. Main DQL

Code Name Data type Group By Purposes Remark
DQL40_sum_not_null_last_row_ASC a number, null, or a list no 1.To sum up one or two fields such as cash and card
2.To add Total in the last row
3.The unique zzz_2018_Year_Total file in the source folder includes the same DVIF fields, where the inline DVJS is.
4.Require SORT file.name ASC
1.The code is copied from DQL10_sum_not_null.
2.To add Total in the last row
3.SORT file.name ASC: To use zzz_2018_Year_Total
4.SORT file.name DESC:To use _____2018_Year_Total


5.The last row Total:111739.79999999999 is to be fixed in the next version

8.2. Metadata: used by DQL40_sum_not_null_last_row_ASC

Summary

8.2.1. 00_report

  • filename : zzz_2018_Year_Total
---
cash: 44703.1
card: 67036.7
---

### zzz_2018_Year_Total
- The report file is ONLY used as the last row of a table by DQL40_sum_not_null_last_row_ASC.
- The file is unique in an Obsidian vault.

The DVIF content in the zzz_2018_Year_Total file will be replaced with inline DVJS.

8.3. Code DQL40_sum_not_null_last_row_ASC

Summary_code
title: DQL40_sum_not_null_last_row_ASC => 1.To sum up one or two fields such as cash and card(type: a number, null, or a list) 2.non-groupBy data 3.To add Total in the last row 4.The unique `zzz_2018_Year_Total` file in the source folder includes the same DVIF fields, where the inline DVJS is. 5.Require `SORT file.name ASC`
collapse: open
icon: 
color: 
```dataview
TABLE WITHOUT ID
	  choice(file.name = "zzz_2018_Year_Total", "**Total**", file.link) AS "File",
      cash AS "Cash",
      card AS "Card",
      sum(default(cash, 0)) as "sum_cash",
      sum(default(card, 0)) as "sum_card",   
      sum(default(cash, 0)) + sum(default(card, 0)) AS "Total"
      
FROM "100_Project/02_dataview/Q01_Sum/Q01_test_data"
WHERE (contains(file.name, "dic_") OR file.name = "zzz_2018_Year_Total")
WHERE file.name != "____2018_Year_Total"
WHERE file.name != this.file.name
WHERE cash != null OR card != null
SORT file.name ASC
```

8.3.1. Screenshots(DQL40)


9. DQL50_sum_not_null_last_row_ASC

Summary

9.1. Main DQL

Code Name Data type Group By Purposes Remark
DQL50_sum_not_null_last_row_ASC a number, null, or a list no 1.To sum up one or two fields such as cash and card
2.To add Total in the last row
3.The unique zzz_2018_Year_Total file in the source folder includes the same DVIF fields, where the inline DVJS is.
4.Require SORT file.name ASC
5.To fix Total:111739.79999999999 which is from the expression 44703.1 + 67036.7
1.The code is copied from DQL40_sum_not_null_last_row_ASC.
2.To add Total in the last row
3.SORT file.name ASC: To use zzz_2018_Year_Total
4.SORT file.name DESC:To use _____2018_Year_Total


5.To fix Total:111739.79999999999

9.2. Metadata: used by DQL50_sum_not_null_last_row_ASC

Summary

9.2.1. 00_report

  • filename : zzz_2018_Year_Total
---
cash: 44703.1
card: 67036.7
---

### zzz_2018_Year_Total
- The report file is ONLY used as the last row of a table by DQL50_sum_not_null_last_row_ASC.
- The file is unique in an Obsidian vault.

The DVIF content in the zzz_2018_Year_Total file will be replaced with inline DVJS.

9.2.2. Notes: a serious bug

```
      choice(file.name = "zzz_2018_Year_Total", round([[zzz_2018_Year_Total]].cash +[[zzz_2018_Year_Total]].card, 1) , sum(default(cash, 0)) + sum(default(card, 0))) AS "Total"
```

9.3. Code DQL50_sum_not_null_last_row_ASC

Summary_code
title: DQL50_sum_not_null_last_row_ASC => 1.To sum up one or two fields such as cash and card(type: a number, null, or a list) 2.non-groupBy data 3.To add Total in the last row 4.The unique `zzz_2018_Year_Total` file in the source folder includes the same DVIF fields, where the inline DVJS is. 5.Require `SORT file.name ASC` 6.To fix Total:111739.79999999999 which is from the expression `44703.1 + 67036.7`
collapse: open
icon: 
color: 
```dataview
TABLE WITHOUT ID
	  choice(file.name = "zzz_2018_Year_Total", "**Total**", file.link) AS "File",
      cash AS "Cash",
      card AS "Card",
      sum(default(cash, 0)) as "sum_cash",
      sum(default(card, 0)) as "sum_card",
      round(sum(default(cash, 0)) + sum(default(card, 0)), 1) AS "Total"
      
FROM "100_Project/02_dataview/Q01_Sum/Q01_test_data"
WHERE (contains(file.name, "dic_") OR file.name = "zzz_2018_Year_Total")         
WHERE file.name != "____2018_Year_Total"
WHERE file.name != this.file.name
WHERE cash != null OR card != null
SORT file.name ASC
```

9.3.1. Screenshots(DQL50)


10. DQL60_sum_not_null_last_row_DESC

Summary

10.1. Main DQL

Code Name Data type Group By Purposes Remark
DQL60_sum_not_null_last_row_DESC a number, null, or a list no 1.To sum up one or two fields such as cash and card
2.To add Total in the last row
3.The unique ____2018_Year_Total file in the source folder includes the same DVIF fields, where the inline DVJS is.
4.Require SORT file.name DESC
5.To fix Total:111739.79999999999 which is from the expression 44703.1 + 67036.7
1.The code is copied from DQL50_sum_not_null_last_row_ASC.
2.To add Total in the last row
3.SORT file.name ASC: To use zzz_2018_Year_Total
4.SORT file.name DESC:To use _____2018_Year_Total


5.To fix Total:111739.79999999999

10.2. Metadata: used by DQL60_sum_not_null_last_row_DESC

Summary

10.2.1. 00_report

  • filename : zzz_2018_Year_Total
---
cash: 44703.1
card: 67036.7
---

### zzz_2018_Year_Total
- The report file is ONLY used as the last row of a table by DQL60_sum_not_null_last_row_DESC.
- The file is unique in an Obsidian vault.

The DVIF content in the zzz_2018_Year_Total file will be replaced with inline DVJS.

10.2.2. Notes: a serious bug

```
      choice(file.name = "____2018_Year_Total", round([[____2018_Year_Total]].cash +[[____2018_Year_Total]].card, 1) , sum(default(cash, 0)) + sum(default(card, 0))) AS "Total"
```

10.3. Code DQL60_sum_not_null_last_row_DESC

Summary_code
title: DQL60_sum_not_null_last_row_DESC => 1.To sum up one or two fields such as cash and card(type: a number, null, or a list) 2.non-groupBy data 3.To add Total in the last row 4.The unique `____2018_Year_Total` file in the source folder includes the same DVIF fields, where the inline DVJS is. 5.Require `SORT file.name DESC` 6.To fix Total:111739.79999999999 which is from the expression `44703.1 + 67036.7`
collapse: open
icon: 
color: 
```dataview
TABLE WITHOUT ID
	  choice(file.name = "____2018_Year_Total", "**Total**", file.link) AS "File",
      cash AS "Cash",
      card AS "Card",
      sum(default(cash, 0)) as "sum_cash",
      sum(default(card, 0)) as "sum_card",
      round(sum(default(cash, 0)) + sum(default(card, 0)), 1) AS "Total"
      
FROM "100_Project/02_dataview/Q01_Sum/Q01_test_data"
WHERE (contains(file.name, "dic_") OR file.name = "____2018_Year_Total")
WHERE file.name != "zzz_2018_Year_Total"
WHERE file.name != this.file.name
WHERE cash != null OR card != null
SORT file.name DESC
```

10.3.1. Screenshots(DQL60)


11. Conclusion

Summary
  • According to the code above, there is a summary as follows.
Field Name Data type Group By Purpose DQL Expression DVJS Expression
cash a number, null, or a list of numbers no To sum up one field such as cash sum(default(cash, 0)) AS “sum_cash” let i_Total_cash = dv.func.default(dv.func.sum(pages.cash), 0);
cash a number, null, or a list of numbers yes To sum up one field such as cash sum(map(rows.cash, (e) => sum(default(e, 0)))) AS “sum_cash”
2 Likes

Topic : S02

Summary
  • How to sum up a number or a list of numbers (non-groupBy)(or groupBy) with skipping the null value? PS.To sum up one field

Test

Summary
  • dataview: v0.5.46

2. Overview

Summary

2.1. Main DQL or DVJS

  • The fowllowng table presents the main DQL, DVJS or inline DVJS in the Solutions.
Code Name Data type Group By Purposes Remark
DQL10_sum_number_or_list exercise:
1.a number
2.a list of numbers
no 1.To sum up one field such as exercise
DQL20_sum_number_or_list_GROUP_BY exercise:
1.a number
2.a list of numbers
yes 1.To sum up one field such as exercise
2.group.key to fullname_of_month
DVJS10_sum_number_or_list
_and_TABLE_last_row_Total_seperately
exercise:
1.a number
2.a list of numbers
no 1.To sum up one field such as exercise
2.To display it as a table
3.To get i_Total_exercise
4.To display it as a table and hide the file count in the dataview table
The code =DQL10_sum_number_or_list + DVJS30_sum_number_or_list
_and_TABLE_last_row_ONLY
DVJS15_sum_number_or_list
_and_TABLE_last_row_Total_merged
exercise:
1.a number
2.a list of numbers
no 1.To sum up one field such as exercise
2.To get i_Total_exercise
3.To display it as a table
The code =DVJS10_sum_number_or_list
_and_TABLE_last_row_Total_seperately
DVJS20_sum_number_or_list_groupBy
_and_TABLE_last_row_Total_seperately
exercise:
1.a number
2.a list of numbers
yes 1.To sum up one field such as exercise
2.group.key to fullname_of_month
3.To display it as a table
4.To get i_Total_exercise
5.To display it as a table and hide the file count in the dataview table
The code =DQL20_sum_number_or_list_GROUP_BY + DVJS30_sum_number_or_list
_and_TABLE_last_row_ONLY
DVJS30_sum_number_or_list
_and_TABLE_last_row_ONLY
exercise:
1.a number
2.a list of numbers
no 1.To sum up one field such as exercise in each file
2.To display it as a table and hide the file count in the dataview table
inDVJS10_sum_number_or_list_Total exercise:
1.a number
2.a list of numbers
no 1.To sum up one field such as exercise in each file
2.To display it as a value
3.Inline DVJS
The code is fromDVJS30_sum_number_or_list
_and_TABLE_last_row_ONLY
Summary_Examples

2.2. SUM

2.2.1. SUM = (reduce(list, (a, b) => a + b))

2.2.1.1. a list of non-groupBy data = 1D array
Total_list=`=reduce([1,2,3], (a, b) => a + b)`//=>6
2.2.1.2. a list of groupBy data = 2D array = Array of arrays = AoA
a_List=`=reduce([[1],[2],[3]], (a, b) => a + b)`//=>1, 2, 3
Total_AoA=`=reduce(reduce([[1],[2],[3]], (a, b) => a + b),(a, b) => a + b)`//=>6

2.2.2. SUM

2.2.2.1. a list of non-groupBy data = 1D array
Total_list=`=sum([1,2,3])`//=>6
2.2.2.2. a list of groupBy data = 2D array = Array of arrays = AoA
a_List=`=sum([[1],[2],[3]])`//=>1, 2, 3
Total_AoA=`=sum(sum([[1],[2],[3]]))`//=>6

2.3. Examples

2.3.1. javascript

```javascript
let nResult = null + 10; //=>10
```

2.3.2. Inline DQL

ng_Result=`=null + 10` //=>No implementation found for 'null + number'

2.3.3. Use default to transform null into zero before using sum

nResult= `=default(null, 0) + 10` //=>10

2.3.4. Use number to transform string into number before using sum

nResult= `=sum(number("5000"))` //=>5000

2.3.5. Use split to get /[0-9]+/ from string and use number to transform it before using sum

// #### meta data ####
Price:: Note01 - 5000
Price:: Note02 - 7000
// #### meta data ####

2.3.5.1. Test
Price=`=split("Note01 - 5000", " - ")[1]`//=>5000
Price=`=split("Note01 - 7000", " - ")[1]`//=>7000
2.3.5.2. OK result
total_Price:: **TOTAL** - `=sum(map(this.Price, (e) =>  number(split(e, " - ")[1])))`//=>TOTAL - 12000
2.3.5.3. NG result
total_Price:: **TOTAL** = `=sum(number(this.Price))`//=>TOTAL = 3

2.3.6. Summary

  • Make sure the input data type is number before sum.

3. Input

Summary

3.1. dictionary files:

  • Location: “100_Project/02_dataview/Q01_Sum/20220725_Sum_02/Q01_test_data02”

3.1.1. F02_number_or_list

  • filename : dic_20220202_exercise
---
exercise: 60
---

  • filename : dic_20220207_exercise
---
exercise: [60, 60]
---

3.1.2. A03__list

  • filename : dic_20220302_exercise
---
exercise: [60, 35]
---

  • filename : dic_20220307_exercise
---
exercise: [56, 22]
---

4. DQL10_sum_number_or_list

Summary

4.1. Main DQL

Code Name Data type Group By Purposes Remark
DQL10_sum_number_or_list exercise:
1.a number
2.a list of numbers
no 1.To sum up one field such as exercise

4.2. Code DQL10_sum_number_or_list

Summary_code
title: DQL10_sum_number_or_list => 1.To sum up one field such as exercise(type: a number or a list of numbers) 2.non-groupBy data
collapse: open
icon: 
color: 
```dataview
TABLE WITHOUT ID      
      file.link AS "File",
      file.name AS "file.name",
      sum(exercise) as "sum_exercise",
      exercise AS "exercise"
FROM "100_Project/02_dataview/Q01_Sum/20220725_Sum_02/Q01_test_data02"
WHERE contains(file.name, "dic_")
WHERE file.name != this.file.name
WHERE exercise != null
SORT file.name ASC
```

4.2.1. Screenshots(DQL10)


5. DQL20_sum_number_or_list_GROUP_BY

Summary

5.1. Main DQL

Code Name Data type Group By Purposes Remark
DQL20_sum_number_or_list_GROUP_BY exercise:
1.a number
2.a list of numbers
yes 1.To sum up one field such as exercise
2.group.key to fullname_of_month

5.2. Code DQL20_sum_number_or_list_GROUP_BY

Summary_code
title: DQL20_sum_number_or_list_GROUP_BY =>1.To sum up one field such as exercise(type: a number or a list of numbers) 2.groupBy data 3.group.key to fullname_of_month
collapse: open
icon: 
color: 
```dataview
TABLE WITHOUT ID
      FG_month AS "G_month",
      rows.file.link AS "File",    
      sum(map(rows.exercise, (e) =>  sum(e))) AS "sum_exercise",
      rows.exercise AS "exercise"      

FROM "100_Project/02_dataview/Q01_Sum/20220725_Sum_02/Q01_test_data02"

WHERE contains(file.name, "dic_")
WHERE file.name != this.file.name
WHERE exercise != null
WHERE file.day.year = 2022
SORT file.name ASC

GROUP BY file.day.month AS G_m

FLATTEN {
    "1": "January",
    "2": "February",
    "3": "March",
    "4": "April",
    "5": "May",
    "6": "June",
    "7": "July",
    "8": "August",
    "9": "September",
    "10": "October",
    "11": "November",
    "12": "December"
}[string(G_m)]  AS "FG_month"

```

5.2.1. Screenshots(DQL20)

20220726_DQL20_output_a01


6. DVJS10_sum_number_or_list_and_TABLE_last_row_Total_seperately

Summary

6.1. Main DVJS

Code Name Data type Group By Purposes Remark
DVJS10_sum_number_or_list
_and_TABLE_last_row_Total_seperately
exercise:
1.a number
2.a list of numbers
no 1.To sum up one field such as exercise
2.To display it as a table
3.To get i_Total_exercise
4.To display it as a table and hide the file count in the dataview table
The code =DQL10_sum_number_or_list + DVJS30_sum_number_or_list
_and_TABLE_last_row_ONLY

6.2. Code DVJS10_sum_number_or_list_and_TABLE_last_row_Total_seperately

Summary_code
title: DVJS10_sum_number_or_list_and_TABLE_last_row_Total_seperately => 1.To sum up one field such as exercise(type: a number or a list of numbers) 2.non-groupBy data 3.To display it as a table 4.To get i_Total_exercise 5.To display it as a table and hide the file count in the dataview table
collapse: open
icon: 
color: 
```dataviewjs
// M11. define pages: gather all relevant pages 
// #####################################################################
let pages = dv
    .pages('"100_Project/02_dataview/Q01_Sum/20220725_Sum_02/Q01_test_data02"')
    .where((page) => dv.func.contains(page.file.name, "dic_"))
    .where((page) => page.file.name !== dv.current().file.name)
    .where((page) => page.exercise !== null)
    .sort((page) => page.file.name, "asc");


// ### M21.Output : pages
// #####################################################################
dv.header(2, "M21.Exercise Report in 2022");
dv.table(
    ["N", "File", "file.name", "sum_exercise", "exercise"],
    pages.map((page, index) => [
        index + 1,
        page.file.link ,
        page.file.name,
        dv.func.sum(page.exercise),
        page.exercise,    
    ])
);


// M41. get i_Total_exercise: dv.func.sum(dv.func.default(pages.cash, 0))
// #####################################################################
let i_Total_exercise = dv.func.default(dv.func.sum(pages.exercise), 0);


// M51. Output : i_Total_exercise
// #####################################################################
//dv.span("i_Total_exercise=" + i_Total_exercise);//=>i_Total_exercise=353



// M53. TABLE :i_Total_exercise, i_Total_exercise
// #####################################################################
// dv.table(
//     ["Name", "Price", "Caffeine Content"],
//     [
//         ["Black Coffee", 120, 300],
//         ["Green Tea", 100, 200],
//         ["Apple Juice", 110, 0],
//         ["Iced Chocolate", 130, 0],
//         ["Hot Chocolate", 105, 6],
//     ]
// );

dv.table(
    ["", "exercise", "Total"],
    [
        ["**Total**", i_Total_exercise, i_Total_exercise],
    ]
);


// M61. hide the file count in the `the_nth_of_table` dataview table :
// #####################################################################
let the_nth_of_table = 2;
this.container.querySelectorAll(".table-view-table tr:first-of-type th:first-of-type > span.small-text")[the_nth_of_table-1].style.visibility = "hidden";



```

6.2.1. Screenshots(DVJS10)


7. DVJS15_sum_number_or_list_and_TABLE_last_row_Total_merged

Summary

7.1. Main DVJS

Code Name Data type Group By Purposes Remark
DVJS15_sum_number_or_list
_and_TABLE_last_row_Total_merged
exercise:
1.a number
2.a list of numbers
no 1.To sum up one field such as exercise
2.To get i_Total_exercise
3.To display it as a table
The code =DVJS10_sum_number_or_list
_and_TABLE_last_row_Total_seperately

7.2. Code DVJS15_sum_number_or_list_and_TABLE_last_row_Total_merged

Summary_code
title: DVJS15_sum_number_or_list_and_TABLE_last_row_Total_merged => 1.To sum up one field such as exercise(type: a number or a list of numbers) 2.non-groupBy data 3.To get i_Total_exercise 4.To display it as a table
collapse: open
icon: 
color: 
```dataviewjs
// M11. define pages: gather all relevant pages 
// #####################################################################
let pages = dv
    .pages('"100_Project/02_dataview/Q01_Sum/20220725_Sum_02/Q01_test_data02"')
    .where((page) => dv.func.contains(page.file.name, "dic_"))
    .where((page) => page.file.name !== dv.current().file.name)
    .where((page) => page.exercise !== null)
    .sort((page) => page.file.name, "asc");

// M21. get i_Total_exercise: dv.func.sum(dv.func.default(pages.cash, 0))
// #####################################################################
let i_Total_exercise = dv.func.default(dv.func.sum(pages.exercise), 0);


// ### M31.define : aoa_drinks
// #####################################################################
// dv.table(
//     ["Name", "Price", "Caffeine Content"],
//     [
//         ["Black Coffee", 120, 300],
//         ["Green Tea", 100, 200],
//         ["Apple Juice", 110, 0],
//         ["Iced Chocolate", 130, 0],
//         ["Hot Chocolate", 105, 6],
//     ]
// );

// ["N", "File", "file.name", "sum_exercise", "exercise"]
let aoa_drinks = pages.map((page, index) => [
        index + 1,
        page.file.link ,
        page.file.name,
        dv.func.sum(page.exercise),
        page.exercise,    
    ]);


// ### M33.update aoa_drinks:push the_lat_row_Total
// #####################################################################
// ["N", "File", "file.name", "sum_exercise", "exercise"]
// aoa_drinks.push([
//     "**Total**",
//     "",
//     "",
//     i_Total_exercise,
//     i_Total_exercise,
// ]);

aoa_drinks["values"][aoa_drinks.length] = [
    "**Total**",
    "",
    "",
    i_Total_exercise,
    i_Total_exercise,
];


// M51. TABLE :aoa_drinks
// #####################################################################
// dv.table(
//     ["Name", "Price", "Caffeine Content"],
//     [
//         ["Black Coffee", 120, 300],
//         ["Green Tea", 100, 200],
//         ["Apple Juice", 110, 0],
//         ["Iced Chocolate", 130, 0],
//         ["Hot Chocolate", 105, 6],
//     ]
// );

dv.header(2, "M51.Exercise Report in 2022");
dv.table(["N", "File", "file.name", "sum_exercise", "exercise"], aoa_drinks);



```

7.2.1. Screenshots(DVJS15)


8. DVJS20_sum_number_or_list_groupBy_and_TABLE_last_row_Total_seperately

Summary

8.1. Main DVJS

Code Name Data type Group By Purposes Remark
DVJS20_sum_number_or_list_groupBy
_and_TABLE_last_row_Total_seperately
exercise:
1.a number
2.a list of numbers
yes 1.To sum up one field such as exercise
2.group.key to fullname_of_month
3.To display it as a table
4.To get i_Total_exercise
5.To display it as a table and hide the file count in the dataview table
The code =DQL20_sum_number_or_list_GROUP_BY + DVJS30_sum_number_or_list
_and_TABLE_last_row_ONLY

8.2. Code DVJS20_sum_number_or_list_groupBy_and_TABLE_last_row_Total_seperately

Summary_code
title: DVJS20_sum_number_or_list_groupBy_and_TABLE_last_row_Total_seperately => 1.To sum up one field such as exercise(type: a number or a list of numbers) 2.groupBy data 3.group.key to fullname_of_month 4.To display it as a table 5.To get i_Total_exercise 6.To display it as a table and hide the file count in the dataview table
collapse: open
icon: 
color: 
```dataviewjs
// M11. define pages: gather all relevant pages 
// #####################################################################
let pages = dv
    .pages('"100_Project/02_dataview/Q01_Sum/20220725_Sum_02/Q01_test_data02"')
    .where((page) => dv.func.contains(page.file.name, "dic_"))
    .where((page) => page.file.name !== dv.current().file.name)
    .where((page) => page.exercise !== null)
    .where((page) => page.file.day.year === 2022)
    .sort((page) => page.file.name, "asc");


// M13. GROUP BY file.day.month:
// #####################################################################
dv.header(2, "M13.Exercise Report groupBy Month in 2022");
for (let group of pages.groupBy((page) => page.file.day.month)) {
    
    // M13.FR10 transform the group.key into the fullname_of_month:
    // #####################################################################
    //dv.header(3, group.key);
    dv.header(3, 
        {
          "1": "January",
          "2": "February",
          "3": "March",
          "4": "April",
          "5": "May",
          "6": "June",
          "7": "July",
          "8": "August",
          "9": "September",
          "10": "October",
          "11": "November",
          "12": "December"
        }[group.key]
    );
    
    
    // M13.FR12 TABLE:
    // #####################################################################
    dv.table(
        ["N", "File", "sum_exercise", "exercise"],
        group.rows
            .sort((k) => k.file.name, "desc")
            .map((k, index) => [
                index + 1,
                k.file.link,
                dv.func.sum(k.exercise),
                k["exercise"],
            ])
    );
}


// M41. get i_Total_exercise: dv.func.sum(dv.func.default(pages.cash, 0))
// #####################################################################
let i_Total_exercise = dv.func.default(dv.func.sum(pages.exercise), 0);


// M51. Output : i_Total_exercise
// #####################################################################
//dv.span("i_Total_exercise=" + i_Total_exercise);//=>i_Total_exercise=353



// M53. TABLE :i_Total_exercise, i_Total_exercise
// #####################################################################
// dv.table(
//     ["Name", "Price", "Caffeine Content"],
//     [
//         ["Black Coffee", 120, 300],
//         ["Green Tea", 100, 200],
//         ["Apple Juice", 110, 0],
//         ["Iced Chocolate", 130, 0],
//         ["Hot Chocolate", 105, 6],
//     ]
// );

dv.table(
    ["", "exercise", "Total"],
    [
        ["**Total**", i_Total_exercise, i_Total_exercise],
    ]
);


// M61. get te counts of unique file.day.month : page.file.day.month
// #####################################################################
let a_unique_months = pages.file.day.month.where((e) => !(this[e] = e in this));
//dv.span(a_unique_months);//=>2,3


// M63. get te counts of unique file.day.month : page.file.day.month
// #####################################################################
let i_counts_of_month = a_unique_months.length;
//dv.span(i_counts_of_month);//=>2


// M65. hide the file count in the `the_nth_of_table` dataview table :
// #####################################################################
let the_nth_of_table = i_counts_of_month + 1 ;
this.container.querySelectorAll(".table-view-table tr:first-of-type th:first-of-type > span.small-text")[the_nth_of_table-1].style.visibility = "hidden";



```

8.2.1. Screenshots(DVJS20)


9. DVJS30_sum_number_or_list_and_TABLE_last_row_ONLY

Summary

9.1. Main DQL

Code Name Data type Group By Purposes Remark
DVJS30_sum_number_or_list
_and_TABLE_last_row_ONLY
exercise:
1.a number
2.a list of numbers
no 1.To sum up one field such as exercise in each file
2.To display it as a table and hide the file count in the dataview table

9.2. Code DVJS30_sum_number_or_list_and_TABLE_last_row_ONLY

Summary_code
title: DVJS30_sum_number_or_list_and_TABLE_last_row_ONLY => 1.To sum up one field such as exercise(type: a number or a list of numbers) in each file 2.non-groupBy data 3.To display it as a table and hide the file count in the dataview table
collapse: open
icon: 
color: 
```dataviewjs
// M11. define pages: gather all relevant pages 
// #####################################################################
let pages = dv
    .pages('"100_Project/02_dataview/Q01_Sum/20220725_Sum_02/Q01_test_data02"')
    .where((page) => dv.func.contains(page.file.name, "dic_"))
    .where((page) => page.file.name !== dv.current().file.name)
    .where((page) => page.exercise !== null)
    .where((page) => page.file.day.year === 2022);
    

// M21. get i_Total_exercise: dv.func.sum(dv.func.default(pages.cash, 0))
// #####################################################################
let i_Total_exercise = dv.func.default(dv.func.sum(pages.exercise), 0);


// M31. Output : i_Total_exercise
// #####################################################################
//dv.span("i_Total_exercise=" + i_Total_exercise);//=>i_Total_exercise=353



// M41. TABLE :i_Total_exercise, i_Total_exercise
// #####################################################################
// dv.table(
//     ["Name", "Price", "Caffeine Content"],
//     [
//         ["Black Coffee", 120, 300],
//         ["Green Tea", 100, 200],
//         ["Apple Juice", 110, 0],
//         ["Iced Chocolate", 130, 0],
//         ["Hot Chocolate", 105, 6],
//     ]
// );

dv.header(2, "M41.Exercise Report in 2022");
dv.table(
    ["", "exercise", "Total"],
    [
        ["**Total**", i_Total_exercise, i_Total_exercise],
    ]
);


// M51. hide the file count in the `the_nth_of_table` dataview table :
// #####################################################################
let the_nth_of_table = 1;
this.container.querySelectorAll(".table-view-table tr:first-of-type th:first-of-type > span.small-text")[the_nth_of_table-1].style.visibility = "hidden";


```

9.2.1. Screenshots(DVJS30)


10. inDVJS10_sum_number_or_list_Total

Summary

10.1. Main Inline DVJS

Code Name Data type Group By Purposes Remark
inDVJS10_sum_number_or_list_Total exercise:
1.a number
2.a list of numbers
no 1.To sum up one field such as exercise in each file
2.To display it as a value
3.Inline DVJS
The code is fromDVJS30_sum_number_or_list
_and_TABLE_last_row_ONLY

10.2. Notes

Notes:
1.copy step M11 and M21 from DVJS30_sum_number_or_list_and_TABLE_last_row_ONLY into an Inline DVJS expression
2.Remove the comments .
3.Add dv.span(i_Total_exercise); in the last row in the Inline DVJS expression.
4.Remove the blank lines.
5.An Inline DVJS is finished!

10.3. Code inDVJS10_sum_number_or_list_Total

Summary_code
Total=`$=
let pages = dv    .pages('"100_Project/02_dataview/Q01_Sum/20220725_Sum_02/Q01_test_data02"')
    .where((page) => dv.func.contains(page.file.name, "dic_"))
    .where((page) => page.file.name !== dv.current().file.name)
    .where((page) => page.exercise !== null)
    .where((page) => page.file.day.year === 2022);     
let i_Total_exercise = dv.func.default(dv.func.sum(pages.exercise), 0);
dv.span(i_Total_exercise);
`

10.3.1. Screenshots(inDVJS10)

//=>353


13. Conclusion

Summary

13.1. Sum

  • According to the codes above, there is a summary as follows.
Field Name Data type Group By Purpose DQL Expression DVJS Expression (Table vs. last_row_Total) Reamrk
exercise 1.a number
2.a list of numbers
no To sum up one field sum(exercise) AS “sum_exercise” 1.M21: (not using GROUP BY)
dv.func.sum(page.exercise),

2.M41: (not using GROUP BY)
let i_Total_exercise = dv.func.default(dv.func.sum(pages.exercise), 0);
1.S02 > DQL10

2.S2 > DVJS10
exercise 1.a number
2.a list of numbers
yes
(once)
To sum up one field sum(map(rows.exercise, (e) => sum(e))) AS “sum_list_G1_11” 1.M13.FR12: (using GROUP BY once)
dv.func.sum(k.exercise),

2.M41: (not using GROUP BY)
let i_Total_exercise = dv.func.default(dv.func.sum(pages.exercise), 0);
1.S02 > DQL20

2.S02 > DVJS20

13.2. To group by the data (once)

Field Name Data type Group By Purpose DQL Expression
exercise 1.a number
2.a list of numbers
yes To groupBy year GROUP BY file.day.year AS G_year
exercise 1.a number
2.a list of numbers
yes To groupBy month
(For same year only)
GROUP BY file.day.month AS G_month
exercise 1.a number
2.a list of numbers
yes To groupBy calendarweek FLATTEN dateformat(file.day, “yyyy-WW”) AS F_calendarweek

GROUP BY F_calendarweek AS GF_calendarweek

14. Reference

Summary

2 Likes

Topic : S03

Summary
  • How to sum up one field such as exercise(type: a number or a list of numbers) without/with using groupBy?
  • How to display the result as a bar chart? (Require obsidian-charts v3.6.2)

Test

Summary
  • dataview v0.5.46
  • obsidian-charts v3.6.2

Input

Summary

dictionary files:

  • Location: “100_Project/02_dataview/Q01_Sum/20220731_Sum_03/Q01_test_data03”

03_number

  • filename : dic_20170301
---
Date: 2017-03-01
exercise:  60
---



  • filename : dic_20170306
---
Date: 2017-03-06
exercise: 120
---



04_list

  • filename : dic_20170401
---
Date: 2017-04-01
exercise: [60, 35]
---



  • filename : dic_20170406
---
Date: 2017-04-06
exercise: [56, 22]
---


08_null

  • filename : dic_20170801
---
Date: 2017-08-01
exercise:  
---



09_undefined

  • filename : dic_20170901
---
Date: 2017-09-01
  
---



DVJS10_sum_number_or_list_and_bar_chart

Summary

Main DVJS

Code Name Data type Group By Purposes Remark
DVJS10_sum_number_or_list
_and_bar_chart
exercise:
1.a number
2.a list of numbers
no
1.Require obsidian-charts v3.6.2
2.To sum up one field such as exercise
3.To display it as a bar chart
The code is from DVJS10_sum_number_or_list
_and_TABLE_last_row_Total_seperately in the following topic.
- 20220725_Solutions

Code DVJS10_sum_number_or_list_and_bar_chart

Summary_code
title: DVJS10_sum_number_or_list_and_bar_chart => 1.Require obsidian-charts v3.6.2 2.To sum up one field such as exercise(type: a number or a list of numbers) 3.non-groupBy data 4.To display it as a bar chart 
collapse: open
icon: 
color: 
```dataviewjs
// M11. define pages: gather all relevant pages 
// #####################################################################
let pages = dv
    .pages('"100_Project/02_dataview/Q01_Sum/20220731_Sum_03/Q01_test_data03"')
    .where((page) => dv.func.contains(page.file.name, "dic_"))
    .where((page) => page.file.name !== dv.current().file.name)
    .where((page) => page.exercise)
    .where((page) => page.file.day.year === 2017)
    .sort((page) => page.file.name, "asc");


// M21. define s_label_of_chart for the chart: excel chart title
// ### Used by obsidian-charts v3.6.2 ###
// #######################################################
let s_label_of_chart = "M41.Exercise Report in 2017";


// M23. define a_labels_of_chart for the chart: excel Category X-axis
// ### Used by obsidian-charts v3.6.2 ###
// use .array() to transform a dataview array into a JavaScript array
// dv_array.array() = dv_array.values = dv_array["values"]
// #######################################################
// ["N", "File", "file.name", "sum_exercise", "exercise"],
let a_labels_of_chart = pages.file.day
    .map((e) => e.toFormat("yyyy-MM-dd"))
    .array();


// #######################################################
// #######################################################
// M23.DEB10 Debug Output: a_labels_of_chart
// #######################################################
// #######################################################

// M23.DEB12 Debug Output: a_labels_of_chart: before using `.array()`
// #######################################################
// {
//   "values": [
//     "2022-02-02",
//     "2022-02-07",
//     "2022-03-02",
//     "2022-03-07"
//   ],
//   "length": 4
// }


// M23.DEB14 Debug Output: a_labels_of_chart: after using `.array()`
// #######################################################
// The following is the content of the a_labels_of_chart.
// [
//   "2022-02-02",
//   "2022-02-07",
//   "2022-03-02",
//   "2022-03-07"
// ]




// M25.define a_data_of_chart for the chart: excel data series
// ### Used by obsidian-charts v3.6.2 ###
// use .array() to transform a dataview array into a JavaScript array
// dv_array.array() = dv_array.values = dv_array["values"]
// #######################################################
let a_data_of_chart = pages.map((page) => dv.func.sum(page.exercise)).array();


// #######################################################
// #######################################################
// M25.DEB10 Debug Output: a_data_of_chart
// #######################################################
// #######################################################

// M25.DEB13 Debug Output: a_data_of_chart: before using `.array()`
// #######################################################
// {
//     "values": [
//       60,
//       120,
//       95,
//       78
//     ],
//     "length": 4
//   }


// M25.DEB15 Debug Output: a_data_of_chart: after using `.array()`
// #######################################################
// [
//     60,
//     120,
//     95,
//     78
// ]




// M41.setup the data for the chart: No modification required
// Use PicPick:Color Picker to get rgb: [Color Picker](https://picpick.app/en/)
// #######################################################
const chartData = {
    labels: a_labels_of_chart,
    datasets: [
        {
            label: s_label_of_chart,
            data: a_data_of_chart,
            backgroundColor: [
                "rgba(230, 142, 147, 0.786)", //01:orange
                "rgba(123, 194, 200, 0.786)", //02:pink greenish
                "rgba(202, 145, 212, 0.786)", //03:pink purple
                "rgba(116, 171, 219, 0.786)", //04:pink blue
                "rgba(229, 168, 116, 0.786)", //05:pink orange
                "rgba(104, 103, 172, 0.786)", //06:pink purple 02
                "rgba(81, 196, 211, 0.786)",  //07:pink greenish 02            

            ],
            borderColor: [
                "rgba(230, 142, 147, 1.0)", //01:orange
                "rgba(123, 194, 200, 1.0)", //02:pink greenish
                "rgba(202, 145, 212, 1.0)", //03:pink purple
                "rgba(116, 171, 219, 1.0)", //04:pink blue
                "rgba(229, 168, 116, 1.0)", //05:pink orange
                "rgba(104, 103, 172, 1.0)", //06:pink purple 02
                "rgba(81, 196, 211, 1.0)",  //07:pink greenish 02

            ],
            borderWidth: 2,
        },
    ],
};

// M43. configure the chart: No modification required
// #######################################################
const config = {
    type: "bar",
    data: chartData,
};

// M45. render the chart: No modification required
// #######################################################
window.renderChart(config, this.container);


// M91.TABLE : pages
// #####################################################################
dv.header(2, "M91.Exercise Report in 2017");
dv.table(
    ["N", "File", "sum_exercise", "exercise"],
    pages.map((page, index) => [
        index + 1,
        page.file.link ,
        dv.func.sum(page.exercise),
        page.exercise,    
    ])
);


```

Screenshots(DVJS10)

Part 1/2 :

Part 2/2 :


DVJS20_sum_number_or_list_groupBy_and_bar_chart

Summary

Main DVJS

Code Name Data type Group By Purposes Remark
DVJS20_sum_number_or_list
_groupBy_and_bar_chart
exercise:
1.a number
2.a list of numbers
yes
1.Require obsidian-charts v3.6.2
2.To sum up one field such as exercise
3.To display it as a bar chart
The code is based on the DVJS20_sum_number_or_list
_groupBy_and_TABLE
_last_row_Total_seperately in the following topic.
- 20220725_Solutions

Code DVJS20_sum_number_or_list_groupBy_and_bar_chart

Summary_code
title: DVJS20_sum_number_or_list_groupBy_and_bar_chart => 1.Require obsidian-charts v3.6.2 2.To sum up one field such as exercise(type: a number or a list of numbers) 3.groupBy data 4.To display it as a bar chart 
collapse: open
icon: 
color: 
```dataviewjs
// M11. define pages: gather all relevant pages 
// #####################################################################
let pages = dv
    .pages('"100_Project/02_dataview/Q01_Sum/20220731_Sum_03/Q01_test_data03"')
    .where((page) => dv.func.contains(page.file.name, "dic_"))
    .where((page) => page.file.name !== dv.current().file.name)
    .where((page) => page.exercise)
    .where((page) => page.file.day.year === 2017)
    .sort((page) => page.file.name, "asc");



// M20. get te counts of unique month: page.file.month ( year = 2022 )
// #####################################################################
let a_unique_months = pages.file.day.month
    .where((e) => !(this[e] = e in this))
    .sort();
//=>[2, 3]


// M21. define s_label_of_chart for the chart: excel chart title
// ### Used by obsidian-charts v3.6.2 ###
// #######################################################
let s_label_of_chart = "M41.Exercise Report in 2017";


// M23. define a_labels_of_chart for the chart: excel Category X-axis
// ### Used by obsidian-charts v3.6.2 ###
// use .array() to transform a dataview array into a JavaScript array
// dv_array.array() = dv_array.values = dv_array["values"]
// #######################################################
// [page.file.day.month]//=>[2, 3]=> ["February", "March"]
let a_labels_of_chart = [];
a_labels_of_chart = a_unique_months.map(
(e) => 
    dv.func.object(
        "1","January",
        "2","February",
        "3","March",
        "4","April",
        "5","May",
        "6","June",
        "7","July",
        "8","August",
        "9","September",
        "10","October",
        "11","November",
        "12","December"
    )[dv.func.string(e)]
);


// M24. Define h_month_exercise_YYYYMM: {M: sum(exercise)} ( year = 2022 )
// #####################################################################
let h_month_exercise_YYYYMM = {};
for (let page of pages) {
    let s_YYYYMM = page.file.day.toFormat("M");
    if (s_YYYYMM in h_month_exercise_YYYYMM) {
        h_month_exercise_YYYYMM[s_YYYYMM] += dv.func.sum(page.exercise);
    } else {
        h_month_exercise_YYYYMM[s_YYYYMM] = dv.func.sum(page.exercise);
    }
}



// M24.DEB01 Debug Output: h_month_exercise_YYYYMM
// ######################################################
// The following is the content of the h_month_exercise_YYYYMM.
// {
//     "2": 180,
//     "3": 173
// }


// M25.define a_data_of_chart for the chart: excel data series
// ### Used by obsidian-charts v3.6.2 ###
// use .array() to transform a dataview array into a JavaScript array
// dv_array.array() = dv_array.values = dv_array["values"]
// #######################################################
//let a_data_of_chart = pages.map((page) => dv.func.sum(page.exercise)).array();
let a_data_of_chart = [];
a_data_of_chart = a_unique_months.map((e) => h_month_exercise_YYYYMM[dv.func.string(e)]).array();



// M25.DEB01 Debug Output: a_data_of_chart
// ######################################################
// The following is the content of the a_data_of_chart.
// [
//     180,
//     173
// ]


// M41.setup the data for the chart: No modification required
// Use PicPick:Color Picker to get rgb: [Color Picker](https://picpick.app/en/)
// #######################################################
const chartData = {
    labels: a_labels_of_chart,
    datasets: [
        {
            label: s_label_of_chart,
            data: a_data_of_chart,
            backgroundColor: [
                "rgba(230, 142, 147, 0.786)", //01:orange
                "rgba(123, 194, 200, 0.786)", //02:pink greenish
                "rgba(202, 145, 212, 0.786)", //03:pink purple
                "rgba(116, 171, 219, 0.786)", //04:pink blue
                "rgba(229, 168, 116, 0.786)", //05:pink orange
                "rgba(104, 103, 172, 0.786)", //06:pink purple 02
                "rgba(81, 196, 211, 0.786)",  //07:pink greenish 02               

            ],
            borderColor: [
                "rgba(230, 142, 147, 1.0)", //01:orange
                "rgba(123, 194, 200, 1.0)", //02:pink greenish
                "rgba(202, 145, 212, 1.0)", //03:pink purple
                "rgba(116, 171, 219, 1.0)", //04:pink blue
                "rgba(229, 168, 116, 1.0)", //05:pink orange
                "rgba(104, 103, 172, 1.0)", //06:pink purple 02
                "rgba(81, 196, 211, 1.0)",  //07:pink greenish 02

            ],
            borderWidth: 2,
        },
    ],
};

// M43. configure the chart: No modification required
// #######################################################
const config = {
    type: "bar",
    data: chartData,
};

// M45. render the chart: No modification required
// #######################################################
window.renderChart(config, this.container);


// M91. GROUP BY file.day.month + TABLE :
// #####################################################################
dv.header(2, "M13.Exercise Report groupBy Month in 2017");
for (let group of pages.groupBy((page) => page.file.day.month)) {
    //dv.header(3, group.key);
    dv.header(3, 
        {
          "1": "January",
          "2": "February",
          "3": "March",
          "4": "April",
          "5": "May",
          "6": "June",
          "7": "July",
          "8": "August",
          "9": "September",
          "10": "October",
          "11": "November",
          "12": "December"
        }[group.key]
    );
    
    
    dv.table(
        ["N", "File", "sum_exercise", "exercise"],
        group.rows
            .sort((k) => k.file.name, "desc")
            .map((k, index) => [
                index + 1,
                k.file.link,
                dv.func.sum(k.exercise),
                k["exercise"],
            ])
    );
}



```

Screenshots(DVJS20)

Part 1/2 :

Part 2/2 :


Reference

Summary

2 Likes

As far as I can see your question hasn’t been answered yet… In case you haven’t found the solution so far, the correct syntax, I think, should be:

LIST
WHERE contains(type,"grammar")
2 Likes

Thank you very much for reviewing my question! I haven’t figure it out. Thank you this helped me to continue on my db!

YAML link vs. DVIF link