Dataview plugin snippet showcase

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

Hi all, I’m having trouble getting the ‘Find All Direct And Indirectly Linked Pages’ example from Codeblock Examples - Dataview to work.

When I cut and paste the codeblock, adding the three ticks and using ‘dataviewjs’ all I get is a really think block with no visible content (see screenshot - this is just a sliver of the ‘edit code’ button)

.

On the page where the code is pasted are 2 obsidian links and 1 embedded pdf, which I would have expected to be returned in the results…

Thank you for any help or advice you may have.

This is going to be hard to investigate! Did you paste as plain text or could some extra stuff from the web have gotten included? Can you share a screenshot with the code visible (maybe switch from Live Preview to Source View)? That might make it easier for folks to help!

Thank you @scholarInTraining for your reply. I just clicked the ‘copy to clipboard’ icon in the top-right hand corner of the code block in github. Here is a screenshot of the code visible.

This is the code block from github:

There are some differences in formatting but this (I think) has more to do with the window size of the code block in Obsidian.

Thank you for posting that! Yes, you are right and I was very off-target in my guess.
There is no output because the codeblock example is missing any lines of code to actually output the data! For something very simple, try dv.table(['Note'], data.map(p => [p.file.link])); which should give you a one-column table (column title “Note”) with the links to each of the files in the results.

Thank you @scholarInTraining. Where exactly to I put the line of code? I assumed it was the last line of code but that didn’t work. This…


produced this error…

Thanks again for your help.

You guessed correctly, but I think the original last line of code got deleted in the process. It started let data = . That line defines data, so its deletion is causing the error. Then my new line of code goes at the very end.

So the correct code should look as follows?



let page = dv.current().file.path;
let pages = new Set();

let stack = [page];
while (stack.length > 0) {
    let elem = stack.pop();
    let meta = dv.page(elem);
    if (!meta) continue;

    for (let inlink of meta.file.inlinks.concat(meta.file.outlinks).array()) {
	    console.log(inlink);
        if (pages.has(inlink.path)) continue;
        pages.add(inlink.path);
        stack.push(inlink.path);
    }
}

let data = dv.array(Array.from(pages)).map(p => dv.page(p));

dv.table(['Note'], data.map(p => [p.file.link]));

That doesn’t work in my case and results in the following error message:

dataviewjs_error

Thanks again @scholarInTraining. I seem to be having the same issue as @altagsverstand.

This is the code I am now using:

And this is the error I receive:

Hi everyone,
Ive been reading here a lot while trying to find out a solution for my case, but so far JavaScript isnt my best friend.

I want to use the „heatmap“ plugin with a dataviewjs query to search data inside of tags. I have an example from the „tracker“ plugin.

Example Daily:
#mood:7
#pushups_sets_count:3/15

—> How would you read the data in dataviewjs to show it in the heatmap?

Would be happy about tipps.

1 Like

Ah, sorry @aarontimo and @alltagsverstand - let me investigate and then I will edit this post if I can figure it out! Progress when the error message changes, but not as satisfying as getting it to work.

@ampxtreme There is a separate thread for datavewjs, but honestly I think you are more likely to get help if you start a new thread that mentions the plugins you want to combine in the title.

EDIT: @aarontimo and @alltagsverstand sorry for the delay - I figured out the issue! The example code will include links to files that do not yet exist in your vault, making some elements of that final “data” array have the value undefined. If I filtered those out, my table worked.
NEW last two lines, filtering out the undefined pages by checking that they exist (undefined is “false-y” in Javascript):

let data = dv.array(Array.from(pages)).map(p => dv.page(p)).filter(p => p);
dv.table(['Note'], data.map(p => [p.file.link]));
1 Like

If anyone ever searches for this and finds this question like I did, being disappointed that it never got an answer:
somebody has kindly sent me the CSS that enables this:

4 Likes