How to make logs on the same page to be separated? For sum expenses group by type

wanted result

I want each log on the same page to be separated and type as “food” and “work” instead of [food, work, food] so that I can calculate how much I spent on each type of expenses.

My logs are like this

In file 2022-09-21:

#spend 
Amount:: 20
Info:: restaurant A
Type:: food

Amount:: 40
Info:: book A
Type:: work

Amount:: 15
Info:: restaurant B
Type:: food

In file 2022-09-22:

#spend 
Amount:: 22
Info:: restaurant C
Type:: food

Amount:: 30
Info:: book B
Type:: work

Amount:: 17
Info:: restaurant D
Type:: food

In file 2022-09-20

#spend 
Amount:: 11
Type:: test

My attempts

dataview version

code

TABLE 
	sum(sum(number(rows.Amount))) AS amount,
	typeof(rows.type),
	rows.type,
	rows.type[0],
	length(Rows.type[0])
	
FROM #spend 
GROUP BY type

result

problem

Type is treated as [“food”, “work”, “food”] and “test”, but I want “food”, “work” and “test”.

dataviewjs attempts

code

const data = dv.pages("#spend")

for(let type of data.type) {
	var type_sum = 0
	for(let data_item of data) { 
		for (let j =0; j <data_item.type.length; j++){
			if(data_item.type[j] == type) { type_sum += data_item.amount[j]; } 
		}
	}
	dv.paragraph(type + ' costs: $'+ type_sum);
}

result

problem

  1. It iterates over the type array so there are redundant outputs, I only want each type to be iterated once. To get unique elements in an array js can do const unique_eles = [...new Set(data.map(item => item.group))]; but dataviewjs seemed not to accept this.
  2. For 1 element array (“test”) the amount is wrong, still remains 0.

Here is another attempt:

dataviewjs groupBy

let groups = dv.pages("#spend").groupBy(p => p.type) 
for (let group of groups.sort(g => g.amount, 'desc')) { 
	dv.header(3, group.key); 
    dv.header(4, "---");
}

result

Capture d’écran . 2022-09-23 à 11.04.33 AM

problem

Same problem. But it’s weird because the doc said “if you index into a data array with a field name (like array.field ), it automatically maps every element in the array to field , flattening field if it itself is also an array.”

Correct the result of dataview attempt, should be this (lower letter “rows” instead of “Rows”):

Main problem: you don’t have pseudo-groups of things; you have fields populated with an array of values.

see the explanation in this case:

1 Like

Thanks! However I tried the solution you gave in that post but code gives error message

Err:

Evaluation Error: [email protected][native code]
@
@
[email protected][native code]
@
[email protected][native code]
[email protected]

The example works.
In your case I don’t know what’s the specific problem. First I don’t know if you’re using Live Preview or Reading Mode (never debug things in LP). Then I see a code block: Are querying fields inside a code block? forget it…

You can always relaunch your app, check the plugin version, etc…

EDIT:
And you’re using dataviewjs in dql query?

Here’s the thing I’ve tried: relaunch the app, uninstall and reinstall the plugin to make sure it’s the newest version, using reading mode, codes are in dataviewjs code block like this

TABLE WITHOUT ID rows.file.link[0] AS File, rows.L.Time[0] as Time, rows.L.Scene[1] as Scene, rows.L.Summary[2] as Summary 
FROM #spend1
WHERE file.lists 
FLATTEN file.lists AS L 
WHERE contains(meta(L.section).subpath, "part") 
GROUP BY file.name + meta(L.section).subpath

datas are in plain code block like this:

## part 1 
- Time:: 14:00 
- Scene:: DT-01.01.01 
- Summary:: Summary 1 
## part 2 
- Time:: 16:00 
- Scene:: DT-01.01.02 
- Summary:: Summary 2 
## part 3 
- Time:: 22:00 
- Scene:: DT-01.01.03 
- Summary:: Summary 3

I’ve also tried to put the datas directly in the file, not in code block, but none of them works

We are losing time with basic things:

  • You can’t use code blocks to place your metadata
  • you’re not using JS syntax, you’re using regular dataview queries (DQL), a SQL-like language… so you need to use a dataview code block, not a dataviewjs one.
1 Like

Gosh you are right, I used dataviewjs instead of dataview… Now it works normally! But I still don’t know how to groupBy type and display sum(money).
The code I have is below but that groupBy Type and sum(Money) doesn’t work

```dataview
TABLE WITHOUT ID rows.file.link[0] AS File, rows.L.Money[0] as Money, rows.L.Type[1] as Type, rows.L.Summary[2] as Summary, sum(Money)
FROM #spend1
WHERE file.lists 
FLATTEN file.lists AS L 
WHERE contains(meta(L.section).subpath, "part") 
GROUP BY Type + meta(L.section).subpath

I give you the link to other post mainly to see the explanation about how metadata is structured.
In that example I gave him a hypothesis according the structure he had and his goal.
Each case is a case. With that example my main goal was: “learn something about metadata structure and think which is the best way to place your data according to your goal”.
And your goal has a big difference in comparison with the example: he just needed to display each “pseudo-group” information by row in the table; but in your case you need to cross information between each “pseudo-group”: the type and, mainly, sum the values.
Only this difference implies a BIG change in the way you put your information and other type of query.

All this to say: you need to start with dataview, read the docs, train with more simple queries, understand how metadata is structured, explore what does each command…All this (yes, is a long learning process) before jump to results. If you’re fixed in the results before learn the tool, then you fail in both steps: (1) the step of the structure of metadata (how you insert metadata in your notes) and (2) the step of the queries.

I don’t want to be “paternalist”, it’s only a fair advice. You’ve the main tool - the Obsidian app -… and if you want to explore a little more, using plugins, you need to be patient in the learning process.

Now I can give you one example.

  1. file 2022-09-20

- #spend (amount:: 11) (type:: test)

Lorem ipsum dolor sit amet, ...
  1. file 2022-09-21
Lorem ipsum dolor sit amet, ...

- #spend (amount:: 20) (info:: restaurant A) (type:: food)
- #spend (amount:: 40) (info::  book A) (type:: work)
- #spend (amount:: 15) (info:: restaurant B) (type:: food)
  1. file 2022-09-22
- #spend (amount:: 22) (info:: restaurant C) (type:: food)
- #spend (amount:: 30) (info::  book B) (type:: work)
- #spend (amount:: 17) (info:: restaurant D) (type:: food)

Lorem ipsum dolor sit amet, ...
  1. Now one possible query:
TABLE WITHOUT ID
	Type,
	"**" + sum(rows.Lists.amount) + "**" AS "Total per type",
	rows.Lists.info AS "What/Where",
	rows.Lists.amount AS "Amount",
	rows.file.link AS When
FROM #spend 
FLATTEN file.lists AS Lists
WHERE contains(Lists.tags, "#spend")
GROUP BY Lists.type AS Type
SORT sum(rows.Lists.amount) DESC
  1. Results

1 Like

Omg thank you so much!! I wonder if there’s a dataviewjs equivalence to this dataview code? Since I want to only calculate the amount in one month. and then turn them into pie graph using Obsidian Charts plugin’s dataview API (which only supports dataviewjs)

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