Using advanced tables with dataview for work hours and financial calculation

Hey Everyone,

I’ve been trying to create a system in my obsidian vault that, with the following functionality:

  1. A note hosting a table
  2. The table, manually fed start and end time of work (for example: 03:00|04:32 )
  3. The table, automatically calculates the amount of hours worked (by dividing the minutes by 60, and subtracting the start time from the end time)
  4. All of said output being added up throughout the entire table
  5. The sum x the hourly pay of said job
  6. pulling the revenue sum that has been calculated constantly and in real time to a different note, that would be used to present a progress bar, showing how much of the wanted revenue for the set month has been achieved.

Main issues:

  1. I’m not sure how to get advanced tables to calculate consecutive formulas in that manner, and since I’m not used to writing in syntax, it’s a little daunting even with the formula reference document in the plugin. Any formatting ideas would be more than welcome. I thought the table should contain the data pre-calculation like that:

Client|Date |Start-time-(H)|Start-time-(M)|End-time-(H)| End-time-(M)|
Name|ddmmyy|17 |35 |18 |45 |

With two more columns being calculated and populated by the table:
HrsSum|RevenueSum|

  1. To present the results with a progress bar in relation to the revenue and hour goals of the month, (using dataview I guess?) I would need to manually write the sum of money or hours into another note. Is there any way of automating the process, telling obsidian to somehow pull a calculated number from the table in real time on it’s own?

I know this is somewhat of a clunky question, but I think creating something like this could help a lot of people find their bearing without leaving obsidian for something like Excel. Any Ideas would be welcome

Thank you so much!
Daniel


Here are some formulas I’ve been trying to use for the table
calculation part:

Working:
$>=(30*$-1)
——-The last column equals the one before the last X30

Not Working:
—- $7= sum($5+($6/60))-($3+($4/60))
—- $7=$4/60

I’m aware of the ways you can chain formulas but you cannot divide each cell in a column by 60, as a pre requisite to a follow up addition?

Ideally I’d love to inhabit the 7th column with:
{$5+($6/60}-{$3($4/60)}

And than, the 8th column with:
$6*30

And finally sum the entire $7 and $8 (separately)

Afterwards, I’m hoping to find a way to automatically refer to the resulting cell in another note as the completed number out of a total in a progress bar.

Anyone got ideas on how to make this happen? What am I missing?

Thank you for you time and effort
Daniel

Sorry, meant $7*30 for the 8th column

Hi.
I have a suggestion.
For now forget the progress bar thing. Just focus in the way you enter your values.

My suggestion is: ignore the advanced tables and improve the way you can enter your values as metadata. Because if your values are considered “data” then you can make the wanted calculations with variable conditions.

_work.zip (1.2 KB)

In attached file a folder called _work with two files:

  1. work data.md - the file where you can insert all your values (following a defined specific format - work:: client | date | start | end).

  2. Totals.md - an example file where you can see a way how you can explore with dataview the values you enter in work data.md (I made 3 examples: global, by client, by month):

This is a simple suggestion:

  • the format adopted to enter values can be another;
  • if anyone know well how to work with templater plugin (not my case) can define a template to enter in fast way each line of work:: client | date | start | end
  • ecc

If anyone interested, I can share this in the section “Share & Showcases” (maybe with the help of someone with knowledge in Templater to create an easy way to enter each work:: line). But because I don’t use this kind of stuff I don’t know it it is relevant or not.

3 Likes

Thank you for the swift and detailed suggestion!
I will upload a follow-up comment with the results as soon as I can.

Great post, using this now!

Side note need to change FROM “_work” to FROM “work data” (name of data source note) or FROM “” depending usage

Thanks again for the help, I’ve been trying to expand the system a bit before sharing it with the community. Any idea how i’d go about setting a different pay rate for each client? maybe an if statement?

this is the current global dataview block:

TABLE WITHOUT ID
	Client,
	Date,
	Start,
	End,
	Hours,
	round(Hours * 30, 2) AS Revenue
FROM ""
WHERE work
FLATTEN work AS W
FLATTEN split(W, " \| ")[0] AS Client
FLATTEN split(W, " \| ")[1] AS Date
FLATTEN split(W, " \| ")[2] AS Start
FLATTEN split(W, " \| ")[3] AS End
FLATTEN date(Date + "T" + End) AS Tend
FLATTEN date(Date + "T" + Start) AS Tstart
FLATTEN round((Tend - Tstart).hours, 2) AS Hours

Where are you planning to store the payrate information? Will that replace the “30” in your revenue calculation? If every client has a payrate and you know what Dataview field to get the rate from, you could just plug in that field access.

I can imagine needing an if statement if some clients don’t have a payrate recorded because they are using your default. The equivalent of an if-statement in dataview is a function called choice(...). The arguments to choice are: the condition, what to do if the condition is true, and what to do if the condition is false.

Hope that helps!

Thank you for responding! I think you may have the key to solve it. Indeed I’m looking to replace the permanent number 30 with a variable based on a per-client fee. Currently, this is the data line that is being logged for each hour of work:

work:: Rimon | 2022-04-03 | 09:45 | 15:42

If I add a fee column to that, what would the query syntax need to look like to take it under consideration?

Let’s say this is the new line that includes the fee:
work:: Rimon | 2022-04-03 | 09:45 | 15:42 | 30

I’m a little new to that kind of scripting and I couldn’t find the right spot for it in the documentation.

Thank you so much!
Daniel

Thank you for giving me an example! Combining that with your dataview block from above:

Let’s add another FLATTEN split line to pull out the payrate, and convert it into a number. Then we can use it in the Revenue calculation. I’m also going to temporarily add Payrate as a column displayed in your table, to help us see that it’s being read correctly. That way if Revenue stops working properly, we can look and see pretty easily whether it is the Payrate or the Hours that are looking suspicious.

TABLE WITHOUT ID
	Client,
	Date,
	Start,
	End,
	Hours, 
    Payrate,
	round(Hours * Payrate, 2) AS Revenue
FROM ""
WHERE work
FLATTEN work AS W
FLATTEN split(W, " \| ")[0] AS Client
FLATTEN split(W, " \| ")[1] AS Date
FLATTEN split(W, " \| ")[2] AS Start
FLATTEN split(W, " \| ")[3] AS End
FLATTEN number(split(W, "\| ")[4]) AS Payrate
FLATTEN date(Date + "T" + End) AS Tend
FLATTEN date(Date + "T" + Start) AS Tstart
FLATTEN round((Tend - Tstart).hours, 2) AS Hours

Note: I think this code will fail with an error message while trying to process lines that don’t have a payrate on the end yet! If you need to handle that situation, we can make the FLATTEN ... AS Payrate line more robust and use a choice(length(split(W, "\| ")) > 4, ..., 30 replacing ... with the number(...) bit and then adding the whole choice command where number(...) was (between FLATTEN and AS Payrate).

Thank you! if possible I’d appriciate an example of the last paragraph you wrote since it’s hard for me to interpret it.

Also, for some reason I could not apply it to my ‘globals’ query. I’m not sure why. this is the message I got:

and this is how I formed my query:

TABLE WITHOUT ID
	round(sum(rows.Hours), 2) AS "Total Hours",
	"**" + sum(map(rows.Hours, (r) => r * Payrate)) + "**" AS "Total Revenue"
FROM ""
WHERE work
FLATTEN work AS W
FLATTEN split(W, " \| ")[0] AS Client
FLATTEN split(W, " \| ")[1] AS Date
FLATTEN split(W, " \| ")[2] AS Start
FLATTEN split(W, " \| ")[3] AS End
FLATTEN number(split(W, "\| ")[4]) AS Payrate 
FLATTEN date(Date + "T" + End) AS Tend
FLATTEN date(Date + "T" + Start) AS Tstart
FLATTEN round((Tend - Tstart).hours, 2) AS Hours
GROUP BY abc

Thanks again for all your help! with a bit of luck we could make a really valuable tool to share with the community

1 Like

Were you able to get the revenue with payrate calculation working on a test file that did have the payrate listed?
If using groupby, just like you have rows.Hours, Payrate would also be rows.Payrate. Does it make sense why? Hopefully that plus the below will be your easy fix for your “number * null” error.

EDIT: Here’s the version of the FLATTEN … AS Payrate line that uses choice to deal with cases where there isn’t a Payrate listed by defaulting to 30. Linebreaks for readability on the forum, probably not good for actual code.

FLATTEN choice(
length(split(W, " \| ")) > 4, 
number(split(W, " \| ")[4]), 
30) AS Payrate