Using dataview to show the sum of a column, but replace the quotient of two columns

[Hundred]

What I’m trying to do

I would like to create a dataview query that does the follow:

• Identifies which notes have a value for two properties: “cost” and “splits”
• For notes with both values, divide “cost” by “splits”
• For notes with just “cost”, leave as is
• Add the result of these equations

I’m putting together a gift tracker with dataview. Here are the properties I use for a gift note:

• type - all gifts have the type of “gift”
• recipient - who the gift is for
• cost - price of a gift
• splits - # of people I’m splitting the cost of a gift with
• tags - occasion (eg. #xmas)
• date - date given
• url - link

I currently have a dataview query that lists gifts by recipient and includes splits, cost, and URL:

TABLE WITHOUT ID
recipient, file.link AS "gift", ("$" + cost) as "cost", splits, url
FROM #xmas
WHERE type = "gift" AND !date
SORT recipient ASC

I also have a dataview query to identify the total:

TABLE WITHOUT ID ("$" + round(sum(rows.cost),2)) AS "Total Cost"
FROM #xmas 
WHERE type = "gift" AND !date
GROUP BY true

Things I have tried

In the first view, I attempted to create a new column for (cost / splits) AS “subtotal,” but this only includes notes with values in both fields.

I tried something similar in the total view (rows.cost / rows.split) but it broke the array.

Any ideas?

Thanks in advance for your help on this!

[holroy]

You could try doing something like default(splits, 1) as divider where it would use splits if it has a value, and 1 in the other cases, this should allow you to do cost / divider to get your personal cost. After doing that, you should also be able to group the query and do a sum(rows.personalCost) to get the total sum of those. Does this make sense?

Another option related to summing up the totals has been posted in the thread below, where we add a row to the original table after summing up some columns. You’d need some tinkering related to selecting the correct field, and to adjust the new row with empty columns for you multi column table. In general you shouldn’t to know a lot of javascript though.

How to get Column totals in dataview

My main approach to such an issue is to switch to dataviewjs, and do the extra work on the table within the javascript code there. So here is one attempt on doing this: ## Original query ```dataview TABLE WITHOUT ID file.link as Account, Amount, Payment_rate WHERE file.folder = this.file.folder WHERE number(Amount) ``` ## With summation ```dataviewjs const amounts = await dv.query(` TABLE WITHOUT ID file.link as Account, Amount, Payment_rate WHERE file.folder = this.file.folder WHERE n…

[Hundred]

@holroy

You could try doing something like default(splits, 1) as divider where it would use splits if it has a value, and 1 in the other cases, this should allow you to do cost / divider to get your personal cost.

This worked, so simple! Thank you!

@holroy

After doing that, you should also be able to group the query and do a sum(rows.personalCost) to get the total sum of those. Does this make sense?

Not sure I’m following this one… could you explain what you mean by grouping the query? When I tried your example, I get the following error: “no implementation found for ‘array / array’”

sum(rows.cost / default(rows.splits, 1))

[holroy]

Given (a most likely not working version of your query):

```dataview
TABLE WITHOUT ID
 cost, divider, pCost
FROM #xmas
FLATTEN default(splits, 1) as divider
FLATTEN cost/divider as pCost
WHERE type = "gift" AND !date

One could add a GROUP BY statement and use the sum() function, like in:

```dataview
TABLE WITHOUT ID sum(rows.pCost)
FROM #xmas
FLATTEN default(splits, 1) as divider
FLATTEN cost/divider as pCost
WHERE type = "gift" AND !date
GROUP BY true
```

As we do the GROUP BY all contents are moved into the rows lists, but since our calculation was just one of the column definition, we need to move it into a FLATTEN for storage and grouping, and finally it allows us to do the sum as shown above.

You could possibly simplify the FLATTEN statements into FLATTEN cost/default(splits, 1) as pCost when I think about it.

[Hundred]

This did the trick!!

Thank you so much for the step by step breakdown, I really appreciate it. Going to do some more research on the FLATTEN command, seems super useful.