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

Sonmez · September 16, 2022, 6:59am

Things I have tried

I prepared my September expenses under a folder and in different files for each day. Thus, there is information about my expenditures on each day of September. For example, on September 1, I had 3 different expenses. I fill them according to the template I prepared earlier in the “01 September” file. These include basic information such as where, with which payment method and how much. By the end of the month, there will be 30 different files in the “September” folder. I named these files as follows: 01 September 2022, 02 September 2022, … 30 September 2022. Finally, I opened another file called “Expenditures” in the same folder, different from these 30 files. I created a dataview table inside this file. My goal is to see all my expenses in a table.

What I’m trying to do

I was able to partially achieve this using the relevant codes. I can even see the totals for each day using the “sum” function. However, I would like to see the general total for September somewhere under the table. I couldn’t find a way to do this. In summary; How can I print the grand totals of numerical data kept in separate files in the same folder in another file.

TABLE 
    Spend as "Spend ($)",
    Company,
    Method as "Payment Method"
    sum(Spend) as "Total Spend ($)"
FROM "September"  
WHERE type = "sept_daily"

“sept_daily” is in the metatag of each file.

justdoitcc · September 24, 2022, 7:28am

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)

Sonmez · September 24, 2022, 3:36pm

Thank you so much. This is exactly the solution I wanted. I thought I had done enough research in the community, but I was wrong. Thank you for taking the time to resolve the problem.

system · October 1, 2022, 3:36pm

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.