How to obtain the minimum due date from incompleted tasks?

[KFranco]

I am trying to create a TABLE using dataview. The table would have two main columns, one is the file name, the other is the minimum date of incomplete tasks.

TABLE
	string(file.frontmatter.project) AS "Course",
	dateformat(minby(file.tasks.due, (t) => t), "dd-MMM-yyyy") AS "Due",
FROM #tasks 

I have been struggling how to add the filter function to fit in the minby function. Thanks in advance for your help!

[holroy]

Can’t you simply do dateformat( min(file.tasks.due), "dd-MMM-yyyy") as Due ?

Maybe the following query also could be helpful in you want to filter out just incomplete tasks which have a due date:

```dataview
TABLE rows.task.text, rows.task.due, min(rows.task.due), dateformat( min(rows.task.due), "dd-MM-yyyy")
FLATTEN file.tasks as task
WHERE task.due and !task.completed
GROUP BY task.path
```

More customisation related to how to visualise the output could surely be done, but I just kept it at a minimum here to show the concept.

[KFranco]

Thanks for the advice. However I got the same results… instead of having the minimum date from incomplete tasks, I got the min results from All tasks

Thanks so much for the advice and I will definitely try!

[holroy]

I updated my original reply with an example showing how to do further manipulation on the tasks. Please check it out again.

[KFranco]

haha thanks
saw it when i was typing the reply
Trying it out now

[holroy]

And here is a query which close resembles your original query:

```dataview
TABLE
  dateformat(
    min( map( filter( file.tasks, (t) => !t.completed AND t.due),
              (t) => t.due )),
    "dd-MM-yyyy")
WHERE filter(file.tasks, (t) => t.due)
```

You could possibly extend the WHERE clause, and simplify the min() functions somewhat. So lets refine it yet a little bit:

```dataview
TABLE
  string(file.frontmatter.project) AS "Course",
  dateformat( min(dueDates), "dd-MM-yyyy" )
FLATTEN array(map( filter( file.tasks, (t) => !t.completed AND t.due),
              (t) => t.due )) as dueDates
WHERE length(dueDates)
```

This uses FLATTEN to produce a list dueDates consisting of tasks which have a due date and are not completed. The final table will only show rows where this list has any elements, and then we’ll find the minimum of those dates, and display it in your wanted format. For good measures, I’ve also added your “Course” column…

[holroy]

@KFranco

I tried to modify it based on the example. However it seems that it still returns the earliest date of all tasks, not the incomplete one.

Either of my last queries will return the lowest due date of any given file containing incomplete tasks with given due dates.

Do you in addition need to group files, and pull out the minimum due date from a set of files? If so, how do you group the files?

(NB! I just saw that you do FROM #tasks, which in my queries needs to be done before the FLATTEN lines. )

[KFranco]

Try that just now…if i put the FLATTEN before FROM, it returns “unrecongized query operation ‘tag’”

I was trying to create a table with all the courses with the earliest due date.
However, each course has it own files so I don’t have to pull dates from multiple files. All I need is to get the min(date) for a single file.

[holroy]

First FROM then FLATTEN. Sorry, if that was badly written.

As long as the tasks are gathered in the same file, the refined query should work (if you add the FROM #tasks before the FLATTEN).

[KFranco]

Thanks for the clarification and sorry for misunderstanding it.

I tried to reduce the first one to

```dataview
TABLE
map( filter( file.tasks, (t) => !t.complete AND t.due),
              (t) => t.due )
WHERE filter(file.tasks, (t) => t.due)

It still gives me two dates, 25th Mar (completed tasks) and 1st May (incomplete one)

[holroy]

And what about this refined query?

```dataview
TABLE
  string(file.frontmatter.project) AS "Course",
  dateformat( min(dueDates), "dd-MM-yyyy" )
FROM #tasks
FLATTEN array(map( filter( file.tasks, (t) => !t.completed AND t.due),
              (t) => t.due )) as dueDates
WHERE length(dueDates)
```

[KFranco]

Same results.
I actually have another column

length(filter(file.tasks, (t) => !t.completed)) AS 🔳

it does show that there is only one task left

[KFranco]

My apology
I found the issue. should be !t.completed

Should have checked that more carefully and It worked now!

Thanks a million holroy!!

[holroy]

Oopsie… My bad, I tested on very full set of tasks, so I didn’t check the status of these tasks, so !t.complete would return a positive value (but not the uncompleted tasks).

I’m going to update the queries for the posterity, sorry for the confusion.

[KFranco]

Nah my bad. Thanks so much for your help!!