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

[Rachel1]

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.

[Rachel1]

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

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.”

[Rachel1]

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

[mnvwvnm]

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

[mnvwvnm]

see the explanation in this case:

Dataview: make a table with a separate row for different values of a key

What I’m trying to do I have notes with several instances of the same key but a different value. I was hoping to have a dataview query that makes a row, repeating the file name, for every instance of the key. I want to order both filename and key. Example sample note: # 1 January 1400 - Time:: 14:00 - Scene:: DT-01.01.01 - Summary:: Summary 1 - Time:: 16:00 - Scene:: DT-01.01.02 - Summary:: Summary 2 - Time:: 22:00 - Scene:: DT-01.01.03 - Summary:: Summary 3 So far I have this query: T…

[Rachel1]

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

Err:

Evaluation Error: eval@[native code]
@
@
asyncFunctionResume@[native code]
@
asyncFunctionResume@[native code]
onload@
…

[mnvwvnm]

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?

[Rachel1]

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

[mnvwvnm]

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.

[Rachel1]

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

[mnvwvnm]

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, ...

2. 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)

3. 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, ...

4. 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

5. Results

[Rachel1]

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)