Use dataview to calculate “days since last time I did 50 pushups”

RAFisherman · March 17, 2023, 6:36am

What I’m trying to do

I want to use data view to get “Days since last push-up workout: 10”.

A push-up workout is one where “pushups::10,20,30” sums to more than 50. I use arrays (10,20,30) because they work well with tables.

But I want to get a list/table/paragraph that’s just the days since last “habit” where habit could be a Boolean or a sum of an array greater than 50.

I use data view tables and feel like I could get this for one habit, but not for multiple.

I think using inline dataviewjs queries is the way to go, but I’ve tried so many things I’m at a loss for sanity.

Things I have tried

something like, this, but this isn’t working:

const today = new Date();

const filteredEntries = dv.pages()
  .where(p => p.file.tags.includes("pushups"))
  .flatMap(p => p.mentions("\\d+", "dv-field"))
  .map(m => parseInt(m.text))
  .filter(pushups => pushups > 50)
  .sortByDate();

const lastEntryDate = filteredEntries.last().date;
const daysSince = Math.floor((today - lastEntryDate) / (1000 * 60 * 60 * 24));

daysSince;

Here’s my structure:
Filmames: (one for each day)

Journal/Workouts/20230301-Workout.md

In-line fields:
Pushups::20,30,10
Sit-ups::10,20,10
date:: [[2023-03-01]]
cardio: true

So I want this:

Days since cardio: 10daya
days since pushups >50: 6 days
days since sit-ups >80: 3 days

I’d kind of prefer if this was in a table, so I’m sure I could put that into a markdown table if I knew the in-line query.

holroy · March 17, 2023, 2:37pm

Do you have many files so far, or are you willing to change your format slightly? There are two ways to make any queries related to this data a whole lot easier to work with, and that is either of the following:

Change the file names to be Journal/Workouts/2023-03-01-Workout.md
Move the date:: into the frontmatter

Either of those actions will set the value of file.day to pick up the date of that particular workout better. I think it would look nicest to change the file name, but that’s me. Would this be something you’re willing to do?

Regarding the actual query I need to think about that for a little while. It’s doing a doubling of requirements, since you want the minimum of days since you done a workout of that exercise, but only some of the workouts counts due to repetition requirement. This make the query a whole lot harder.

The base query for listing the output is somewhat simple though:

```dataview
TABLE file.day, date, sum(pushups), sum(sit-ups), cardio
WHERE file.folder = this.file.folder
WHERE pushups OR sit-ups OR cardio
```

Note that here I’ve included the file.day which would get a value if you change either the file name or moved the date field into the frontmatter.

It’s the grouping of these data which make it a little tricky.

A similar query using dataviewjs:

```dataviewjs
// Key is the exercise, and the array has three items
//   [0] - Number of required repetions, or -2 if only needs to be done
//   [1] - Text for display
//   [2] - Minimum days since last met requirements, -1 is default
const track = {
  "pushups": [ 50, "Days since more than 50 pushups", -1],
  "sit-ups": [ 80, "Days since more than 80 situps", -1],
  "cardio": [ -2, "Days since cardio", -1]
}

const result = dv.pages( `"${ dv.current().file.folder }"` )
  .where(p => Object.keys( track )
                    .filter( t => p[t]).length > 0 )
  .map( p => [p.file.day, 
              p.date, 
              p.pushups.reduce((acc, curr) => acc+curr, 0), 
              p["sit-ups"].reduce((acc, curr) => acc+curr, 0),
              p.cardio ] )

dv.table(["file.day", "date", "sum pushups", "sum situps", "cardio"], result)
```

This has the gist of a concept related to the requirements using the track dictionary, but it’s not a fully grown/matured concept, so this will most likely change. In this query it’s just used to find which fields we want to check for in the file.

The .reduce( (acc, curr) => acc + curr, 0) ) is a way to sum all the values of that array.

In other words, is it feasible for you to change either the file name or move the date into the frontmatter, so that we can get to use file.day ?

And, have I understood it correctly that the main output you want is the lowest number of days since either of the specific exercises met the required goal?

holroy · March 17, 2023, 10:13pm

So, I don’t suspect the following to actually work, but it would be fun (for me, at least) to see if it actually returned anything within your vault:

```dataview
TABLE WITHOUT ID
  (date(today) - date(LastPushup[0])).days + " days ago" as "Last Pushup",
  LastPushup[1] as PushupCount,
  (date(today) - date(LastSitup[0])).days + " days ago" as "Last Situp",
  LastSitup[2] as SitupCount,
  (date(today) - date(LastCardio[0])).days + " days ago" as "Last Cardio"  
  
FLATTEN default(sum(pushups), 0) as sumPushups
FLATTEN choice(sumPushups >= 50, sumPushups, null) as reqPushups
FLATTEN default(sum(sit-ups), 0) as sumSitups
FLATTEN choice(sumSitups >= 80, sumSitups, null) as reqSitups
FLATTEN default(cardio, false) as boolCardio
FLATTEN choice(boolCardio, true, null) as reqCardio
FLATTEN array(
  [date, reqPushups, null, null],
  [date, null, reqSitups, null],
  [date, null, null, reqCardio]) as candidate
WHERE pushups OR sit-ups OR cardio
GROUP BY candidate
WHERE candidate[1] OR candidate[2] OR candidate[3]
GROUP BY true
FLATTEN   array(max(filter(rows.candidate, (r) => r[1]))) as LastPushup
FLATTEN   array(max(filter(rows.candidate, (r) => r[2]))) as LastSitup
FLATTEN   array(max(filter(rows.candidate, (r) => r[3]))) as LastCardio
```

On my rather simple test setup based on your input, this returns the following for me:

So could you please test this query on your data, and see whether it returns anything at all, and if it does return anything if that seems to be the correct output?

RAFisherman · March 18, 2023, 3:48am

Wow, just amazing!!

Im curious what’s happening with the cardio. Because as of this vault, yesterday I did both sit-ups and pushups, but no cardio.

RAFisherman · March 18, 2023, 3:57am

Okay, I had a bug in my files where I didn’t have “cardio”, but it was named something else.

So on 2023-03-15 I added “cardio::” and it doesn’t break. But when I have “cardio:: y” or “cardio:: true” all of it fails:


Dataview: Every row during final data extraction failed with an error; first 3:

            - can only index into links with strings (a.b or a["b"])

RAFisherman · March 18, 2023, 4:02am

Okay, so instead of using booleans for cardio (which I don’t have in my data anyway, I have miles biked from my bike or miles ran), I just treated my cardio as a sum, like sit-ups.

FLATTEN default(sum(kj), 0) as sumKJ
FLATTEN choice(sumKJ>0, sumKJ, null) as reqCardio

Now this works like a charm!!

You’re my hero. Thank you.

holroy · March 18, 2023, 8:51am

Do you (to some extent) understand the query, and what’s happening? Would you be able to extend it with another exercise or similar? (Should I try to explain the logic?)

How do you markup your cardio? You have just that single example of “true” in this thread. And how do you markup, if you didn’t do the cardio?

RAFisherman · March 18, 2023, 4:13pm

Okay, well since you asked

I don’t fully understand the query
I think the query is slightly different than what I want to learn, though it’s certainly helpful.

First, I’m thinking of jurassic park: “They were so occupied with if they ‘could’ they didn’t think to consider if they ‘should’”…

What do I already have/know? I already have a habit tracker like this:

The (car-dio ) column is a case-when statement of sorts:

	choice(
		kj>0, "🚴",
		choice(dur(bballgames) > 0, "⛹🏻‍♂️",
		"⭕️"
	)) as 🏎,

so I don’t have “cardio” in my workout log becuase on a given day i use either my peloton (which records in KJ, or kilojoules), or I go on a run.

The other columns are for pushups or situps .

Now, with your query, I tried adding back (a new workout i want to track).

TABLE WITHOUT ID

(date(today) - date(LastPushup[0])).days + 
  " days ago (" + LastPushup[1] + ")" as Pushup,
(date(today) - date(LastSitup[0])).days + 
	" days ago (" + LastSitup[2] + ")"
	as Situp,
(date(today) - date(LastBack[0])).days + 
	" days ago (" + LastBack[3] + ")"
	as Back,
(date(today) - date(LastCardio[0])).days + 
	" days ago (" + LastCardio[4] + ")" 
	as Cardio

FROM "Journal/Workouts"

FLATTEN default(sum(pushups), 0) as sumPushups
FLATTEN choice(sumPushups >= 100, sumPushups, null) as reqPushups

FLATTEN default(sum(sit-ups), 0) as sumSitups
FLATTEN choice(sumSitups >= 100, sumSitups, null) as reqSitups

FLATTEN default(sum(back), 0) as sumBack
FLATTEN choice(sumBack >= 0, sumBack, null) as reqBack

FLATTEN default(sum(kj), 0) as sumKJ
FLATTEN default(sum(bballgames), 0) as sumBasketball
FLATTEN choice(sumKJ > 0 or sumBasketball>0, true, null) as reqCardio

FLATTEN array(
  [date, reqPushups, null, null, null],
  [date, null, reqSitups, null, null],
  [date, null, null, reqBack, null],
  [date, null, null, null, reqCardio]) as candidate
  
WHERE pushups OR sit-ups OR back OR kj OR bballgames
GROUP BY candidate
WHERE candidate[1] OR candidate[2] OR candidate[3] or candidate[4]
GROUP BY true

FLATTEN array(max(filter(rows.candidate, (r) => r[1]))) as LastPushup
FLATTEN array(max(filter(rows.candidate, (r) => r[2]))) as LastSitup
FLATTEN array(max(filter(rows.candidate, (r) => r[3]))) as LastBack
FLATTEN array(max(filter(rows.candidate, (r) => r[4]))) as LastCardio

Mostly works!

I noticed something weird when developing:

I only recorded ‘back’ for the first time on Monday. And when I only had one record, nothing showed up for some reason. So I added a new record on Sunday so that it would work. So that was strange.

Since you asked, here’s what I don’t know what’s going on (i’m a python/R/SQL developer by day, but am having difficulty translating to what dataviewjs is doing or how it manages data).

what does the double WHERE/GROUP BY statement doing?
I think I can explain the indexing., i.e., date(LastCardio[0]) but lastCardio[4]? What’s going on under the hood? I’m guessing this is taking the array from candidate and it’s getting the 0th index (date) and the 4th index, which has the reqCardio. But why did you need to do a diagonal array on this? what is array(max(filter( doing?
How does this query know to get the latest occurrence? (no sort/filter on dates, just FLATTEN commands…)

Now, back to jurassic park.

I want to know how to query any “last” event using dataviewjs. This seems really useful to me for tracking. Other things I’d like to track:

Last time i called person X call-joe::.
Last time I went to the beach/on a hike, etc.

This query is pretty beefy and hard to update. Is there a way to have an inline query so I can have a simple bullet point for a single tag? Something like this:

Days since last calljoe::: 15
Days since last beach:: trip: 254

where that’s something like =dv.pages("Journal/Workouts/").where(calljoe).sort(filename)[0].date - date(date(today))? in other words: filter all files where calljoe happens, sort by filename (descending?), get the first, then get the date (which is date:: [[2023-03-01]]), then subtract from today’s date.

This approach seems more flexible to me than the table, but I like the table for my workout data. But i have no idea what dv.pages() is really doing and I’d benefit from a translation from javascript data management to python/dictionary/lists.

RAFisherman · March 18, 2023, 4:18pm

and clearly as you look at my habit tracker i’ve been spending way more time on this exercise than actually working out

RAFisherman · March 18, 2023, 4:29pm

Just played around with it, and here’s my new setup:

TABLE WITHOUT ID

"👊 "+(date(today)-date(LastPushup[0])).days+" days ("+LastPushup[1]+")" +
"<br>🆙 "+(date(today)-date(LastSitup[0])).days+" days ("+LastSitup[2]+")" +
"<br>🔙 "+(date(today)-date(LastBack[0])).days+" days ("+LastBack[3]+")" +
"<br>🏎 "+(date(today) - date(LastCardio[0])).days+" days ("+LastCardio[4]+")" 
	as "Days of Rest"

FROM "Journal/Workouts"

FLATTEN default(sum(pushups), 0) as sumPushups
FLATTEN choice(sumPushups >= 100, sumPushups, null) as reqPushups

FLATTEN default(sum(sit-ups), 0) as sumSitups
FLATTEN choice(sumSitups >= 100, sumSitups, null) as reqSitups

FLATTEN default(sum(back), 0) as sumBack
FLATTEN choice(sumBack >= 0, sumBack, null) as reqBack

FLATTEN default(sum(kj), 0) as sumKJ
FLATTEN default(sum(bballgames), 0) as sumBasketball
FLATTEN choice(sumKJ > 0 or sumBasketball > 0, max(sumKJ + "KJ🚴🏻‍♂️", sumBasketball+" games 🏀"), null) as reqCardio

FLATTEN array(
  [date, reqPushups, null, null, null],
  [date, null, reqSitups, null, null],
  [date, null, null, reqBack, null],
  [date, null, null, null, reqCardio]) as candidate
  
WHERE pushups OR sit-ups OR back OR kj OR bballgames
GROUP BY candidate
WHERE candidate[1] OR candidate[2] OR candidate[3] or candidate[4]
GROUP BY true

FLATTEN array(max(filter(rows.candidate, (r) => r[1]))) as LastPushup
FLATTEN array(max(filter(rows.candidate, (r) => r[2]))) as LastSitup
FLATTEN array(max(filter(rows.candidate, (r) => r[3]))) as LastBack
FLATTEN array(max(filter(rows.candidate, (r) => r[4]))) as LastCardio

workout file:

date:: [[2023-03-16]]
duration::     0      minutes

running-dur::    0      minutes

body-notes::
pushups::121
sit-ups:: 120
bballgames::
back:: 

#workout:: 10x10 pushups, 2min rest each. 30x4 sit-ups. Then 20 pushups. 

is-pr::
time::
kj::
avg-power::
avg-resistance::
avg-cadence::
mi::
calories::
bike-notes::

holroy · March 21, 2023, 11:49am

The originally or (initial) query will list one not per row. Through using FLATTEN in various context, and especially in the FLATTEN ... as candidate I expand this into n rows pr note, where n is the number of exercises we’re tracking.

So the first WHERE pushups AND ... is used to require either exercise to be present, and the GROUP BY candidate could possibly be excluded (I don’t quite remember why it was useful), but it does help eliminating non-interesting candidate groups together with the next WHERE candidate[1] AND ... which basically ensure that we end up without any [ date, null, null, null, null ] candidates.

Finally the last GROUP BY true, gathers all the candidate stuff into rows.candidate as we only want to look at the sum information for the candidates. Hopefully, that makes a little more sense.

The diagonal array is linked to the max() usage. Ideally we would like to use max(date) for any exercise above limit. However we need to make a connection between the date the exercise was done, and the actual exercise. By doing the diagonal array we duplicate the date in connection with one specific exercise variant, and thusly we can do max() on a filter set only related to that specific exercise.

So you’re quite right that we’re using filter to pull out those having a value in that exercises column, and since it returns the entire row, it’ll do a max on the row (which just happens to start with the date we’re really interested to get the max value out of).

The final array() in front is to counter the default nature of FLATTEN to change the number of rows in the result set. Sometimes it’s needed, and sometimes not. In this case it was needed, or else I think it produces multiple rows when we really wanted just the one row for all of these.

Just try it without the array(), and it’ll most likely produce some output, which is not wanted, but perhaps helpful for the understanding.

Since we’ve grouped the exercises in lists where the date is directly related to that specific exercise, picking out the maximum value of that list, just happens to be the maximum date (aka closest today (aka the latest occurence)).

So the FLATTEN in most of the lines in this query is actually more to be seen as variable declaration, with some occasional trickery to avoid changing the number of rows in the result set. That is beside the FLATTEN ... as candidate where we intentionally use it to split each row into n exercise candidates.

holroy · March 21, 2023, 12:21pm

This query is a monstrous beast, that’s for sure. And the main reason is that you wanted to pull out the last variant for multiple cases. To keep track of that, one needs to do that “diagonal array” trick to allow for the various cases to co-exist with each other before finally gathering up the pieces and doing the end result.

I did start at writing a dataviewjs for the multiple latest variants, as it presents, but that is also a somewhat large query. However, doing this for single events is another story, and you seem to be on the right track.

Last single event

Using a DQL query

The following untested query could (possibly ) return the result, and it’ll serve a purpose as showing the gist of the idea again:

```dataview
TABLE WITHOUT ID date
FROM "Journal/Workouts"
WHERE calljoe
SORT date DESC
LIMIT 1
```

The idea is simple. List all the dates from where calljoe is defined, sort them by date descending, and only display the first entry, aka the latest.

Using dataviewjs

Now the same within dataviewjs:

```dataviewjs

const lastTime = dv.pages('"Journal/Workouts"')
  .where(p => p.hasOwnProperty("calljoe")
  .sort(p => p.date)
  .limit(1)

dv.span(`Days since last 'calljoe': ${ lastTime["date"] }`)
```

This can also be made into a one liner like: `$= dv.span(“Days since last ‘calljoe’” + dv.pages(‘“Journal/Workouts”’).where(p => p.hasOwnProperty(“calljoe”).sort(p => p.date).limit(1).map(p => p.date) )`. But it’s not very readable like that…

dv.pages() is the base query which in this form lists all pages available in the vault, and then you start limiting it down to the result set you want. If possible, it’s better to do something like dv.pages('"Journal/Workouts"') to have a smaller start set instead of the entire vault.

In the multi-line version of this script you could bail out of the query at any point, and use the result set for other operation. So it’s quite useful to do console.log(lastTime) before the dv.span() and comment out the various line in the daisy chain to see what’s happening at each level. (I hope you’re familiar with looking in the Developers Tool’s console pane. If not, try it out, it’s very useful for debugging) )

Two thoughts for your queries going forward:

Please do consider having a proper date in your file names (or move the date into the frontmatter), as that would enable the usage of file.day which would simplify some of these scripts
Consider doing call:: [[Joe]] instead of calljoe:: something, as the previous would allow for a more generic approach to listing when you called anybody…