Best way of structuring data to get total amount of working hours

[Yngve]

I am a newbie and I’ve been searching and trying to understand, but alas…

I have a daily journal where I write some notes about my customers, what I have done for them and for how many hours.
My inline fields are like this:
[customer:: ], [what:: ], [hours:: ]
I have managed to create a dataview query that lists all these inline fields,

TABLE customer, what, hours 

WHERE type = "daily" AND customer != null
SORT date desc

Then I have tried to summarize, but no total sum unfortunately. It’s obvious that I don’t understand this sentence below:

total::`=round(sum(this.days.hours) ,2)

but I would also like to sum up all the working hours, and that’s where I don’t success…

Any good helpers are much appreciated

[Yngve]

To clarify, each day has it’s own note

[holroy]

When you’re using a format like that to list your notes on that customer, then it’ll generate disconnected lists on each note related to each of the fields. Consider the following note:

[customer:: A], [hours:: 10]
Made a call to [customer:: B]
[customer:: C], [hours:: 12]

Customers: `= this.customer`
Hours: `= this.hours`
Total of hours: `= sum(this.hours) `

Which has this output:

Due to how these fields are connected to the note itself, and not interconnected, you’re not able to tell which hours belongs to which customer, nor which customer actually misses the hour field completely. These fields are example on using the note context for defining the field.

A better approach would be to use the list context to correlate the various fields:

- [customer:: A], [hours:: 10]
- Made a call to [customer:: B]
- [customer:: C], [hours:: 12]
- [customer:: A], [hours:: 5]
- [customer:: C], [hours:: 2]

Customers: `= this.customer`
Hours: `= this.hours`
Total of hours: `= sum(this.hours) `

### All hour related list items from current note
 
```dataview
TABLE WITHOUT ID item.customer, item.hours
FLATTEN file.lists as item
WHERE file = this.file
  AND item.hours
```

### Summed up hour items, grouped by customer

```dataview
TABLE sum(rows.item.hours) as "Total hours"
FLATTEN file.lists as item
WHERE file = this.file
  AND item.hours
GROUP BY item.customer as Customer
```

This example holds two queries, one which just the dedicated items related to hour notes, and which then forgets about the call to “B” since it doesn’t have any hours field related to it (the AND item.hours part of the queries).

In the next query it groups these entries by the customer using GROUP BY item.customer, and this moves all related fields into the rows object. So what was item.hours is now grouped into rows.item.hours which can be used by the sum() to calculate the sum of hours.

These example queries are limited to the current note using file = this.file, so by removing that you’ll look at all of your notes. Note that it’s wise to limit the query to the smallest file set you’re able to, like doing FROM "Daily notes" or similar to avoid scanning your entire vault.