Calculating the grand totals of numbers in different files in "dataview"

[justdoitcc]

Topic

Summary

• How to sum up a number or a list of ungrouped data with skipping the null value?
• How to display the last row Total in the result?

---

Test

Summary

• dataview: v0.5.46

---

Input

Summary

the current note

• filename : 20220924_Q19_DVJS10_Expenditures

### DVJS10

---

dictionary files

• Location: “100_Project/01_dataviewjs/01_by_example/Q19_Sum/Q19_test_data”

09

• filename : dic_20030901

---
Date: 2003-09-01
Spend:  60
Company: USA
Method: cash
type: sept_daily
---

---

• filename : dic_20030906

---
Date: 2003-09-06
Spend:  [60, 60]
Company: USA
Method: cash
type: sept_daily
---

---

• filename : dic_20030911

---
Date: 2003-09-11
Spend:  [60, 35]
Company: USA
Method: cash
type: sept_daily
---

---

• filename : dic_20030916

---
Date: 2003-09-16
Spend:  [56, 22]
Company: USA
Method: cash
type: sept_daily
---

---

09_null

• filename : dic_20030921

---
Date: 2003-09-21
Spend:  
Company: USA
Method: cash
type: sept_daily
---

---

09_undefined

• filename : dic_20030926

---
Date: 2003-09-26

Company: USA
Method: cash
type: sept_daily
---

---

Exercises : DVJS10_sum_null_number_or_list_and_TABLE_last_row_Total_merged

Summary

Main DVJS

Code Name	Data type	Group By	Purposes	Remark
DVJS10_sum_null_number_or_list _and_TABLE_last_row_Total_merged	Spend: null or a number or a list	no	1.To sum up one field such as Spend 2.To get i_Total_Spend 3.To display the result as a table	The DVJS10 is based on the DVJS15 in the following topic. - 20220725_dv_Sum_02:Solutions_partI

Notes

Summary

Step M11

Original Example

// M11. define pages: gather all relevant pages 
// #####################################################################
let pages = dv
    .pages('"100_Project/01_dataviewjs/01_by_example/Q19_Sum/Q19_test_data"')
    .where((page) => page.type === "sept_daily");

Another Example

// M11. define pages: gather all relevant pages 
// #####################################################################
let pages = dv
    .pages('"September"')
    .where((page) => page.type === "sept_daily");

---

Code DVJS10_sum_null_number_or_list_and_TABLE_last_row_Total_merged

Summary_code

title: DVJS10_sum_null_number_or_list_and_TABLE_last_row_Total_merged => 0.Not to use `groupBy` 1.To sum up one field such as `Spend`(type: null or a number or a list) 2.To get `i_Total_Spend` 3.To display the result as a table
collapse: open
icon: 
color: 
```dataviewjs
// M11. define pages: gather all relevant pages 
// #####################################################################
let pages = dv
    .pages('"100_Project/01_dataviewjs/01_by_example/Q19_Sum/Q19_test_data"')
    .where((page) => page.type === "sept_daily");


// M21. get i_Total_Spend: the_last_row_Total
// #####################################################################
let i_Total_Spend = dv.func.default(dv.func.sum(pages.Spend), 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],
//     ]
// );


// ["File", "Spend ($)", "Company", "Payment Method", "Total Spend ($)"]
let aoa_drinks = pages.map((page) => [
    page.file.link,
    page.Spend,
    page.Company,      
    page.Method,     
    dv.func.sum(dv.func.default(page.Spend, 0)),  
]);



// ### M33.update aoa_drinks: push the_last_row_Total
// #####################################################################
// ["File", "Spend ($)", "Company", "Payment Method", "Total Spend ($)"]
// aoa_drinks.push([
//     "**Total**",
//     i_Total_Spend,
//     "",
//     "",
//     i_Total_Spend,
// ]);


// ["File", "Spend ($)", "Company", "Payment Method", "Total Spend ($)"]
aoa_drinks["values"][aoa_drinks.length] = [
    "**Total**",
    i_Total_Spend,
    "",
    "",
    i_Total_Spend,
];


 
// 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.Spend Report in 2003");
dv.table(
    ["File", "Spend ($)", "Company", "Payment Method", "Total Spend ($)"],
    aoa_drinks
);


```

---

Screenshots(DVJS10)

---