Dataview query to count the occurrence of an item

[cheesycrips]

First, search the help docs and this forum. Maybe your question has been answered! The debugging steps can help, too. Still stuck? Delete this line and proceed.

What I’m trying to do

In my daily notes file I keep track of activities I do each day (0=No, 1=Yes). I would like to create a habit tracker query to add up how many consecutive days I have done an activity over the last 90 days. i.e. add up successful days until there is a zero, at which point the counter should be reset to zero and start counting subsequent successful days

[Daily Notes File]
Exercise:: 0
Reading:: 1

Things I have tried

So far I have managed to create a query that adds up the total number of activities for the last 90 days, but I am unsure how to limit it to only a total of consecutive days.

TABLE Streak  
FROM "07 Planner/07.1 Daily"
WHERE file.day <= date(now) AND file.day >= date(now) - dur(90days)
FLATTEN Reading as ReadingTotal 
WHERE Reading 
GROUP BY ReadingTotal
FLATTEN length(rows.ReadingTotal) as Streak

[holroy]

Getting consecutive streaks you’ll need to process the result rows in a row by row base.

This is hard to do (if at all possible) in an ordinary DQL query, but it shouldn’t be too hard using dataviewjs, with either a for loop or some clever use of reduce.

[cheesycrips]

Ok here is my best attempt (new to Javascript) but it does not return any data.

I can use this simple query to return all pages that have Reading::1 - this works fine

dv.list( dv.pages('"07 Planner/07.1 Daily"') .where(row => row.Reading == 1) .file.name )

However when I try to count the number of consecutive days it returns nothing.

let pages = dv.pages('"07 Planner/07.1 Daily"').where(row => row.Reading == 1);
let count = 0;
let currentDate = null;

for (let page of pages) {
    let pageDate = new Date(page.file.mtime);
    let pageDateString = pageDate.toISOString().split('T')[0];
    if (currentDate !== pageDateString) {
        count++;
        currentDate = pageDateString;
    }
}

count;

Anyone have any tips where I am going wrong - besides trying to teach myself Javascript

[cheesycrips]

Ok this is what I settled on - i n case it is of any use to anyone else

const pages = dv.pages('"07 Planner/07.1 Daily"').where(row => row.Reading == 1).map(row => row.file.name);

// Assuming the file names contain date and are in format 'YYYY-MM-DD'
// Sort the pages by date
pages.sort();

let currentStreak = 1; // Start with 1 as we'll be counting the most recent day first

for (let i = pages.length - 1; i > 0; i--) { // Start from the last date and go backwards
    const currentDate = new Date(pages[i]);
    const previousDate = new Date(pages[i - 1]);

    const differenceInDays = (currentDate - previousDate) / (1000 * 60 * 60 * 24); // Difference in days

    if (differenceInDays === 1) {
        currentStreak++;
    } else {
        break; // The moment we find a break in the current streak, we exit the loop
    }
}

dv.header(2, `Current streak of Reading is: ${currentStreak} days`);