Grouping and counting by Dataview

[avliubimov]

Hello, I’m new in Obsidian and have a question.

I have multiple files in folder with next fields

Ticket:: [2064709](uri)
Work Order:: [12463314](uri)
Finish date:: 2024-09-13
State:: Finished

Generally, my task - to create a chart with count finished Tickets by date

But firstly I want to get table with two fields Date and Count

Step 1:

Table WITHOUT id
	row["Finish date"]
FROM "path/to/folder"
WHERE contains(State,"Finished")

• I get one column with all dates (some of them duplicates)

Next I try this:

Table WITHOUT id
	length(rows) as Count,
	row["Finish Date"] as Date
FROM "path/to/folder"
WHERE contains(State,"Finished")
group by Date

I’ve got only one row: in column Count I have total count of rows and - in Date.

Please help me((

[holroy]

A useful trick related to Dataview queries is to think of the first line being the last line. In other words, what you define in the TABLE ... before the FROM line is done at the end of the query. Then you could see that Date isn’t defined.

So try this variant instead:

```dataview 
Table WITHOUT id
	length(rows) as Count, key
FROM "path/to/folder"
WHERE contains(State, "Finished")
FLATTEN row["Finish Date"] as Date
group by Date
```

Now the group by statement has something to work with, and it also stores its result in the key field. Another variant could be to do the following:

```dataview 
Table WITHOUT id
	length(rows) as Count, Date
FROM "path/to/folder"
WHERE contains(State, "Finished")
GROUP BY row["Finish date"] as Date
```

[avliubimov]

Oh, thanks a lot/ The second vay is exactly what I need.

And one tiny question more. I have date in format yyyy-mm-dd, if I do

...
dateformat(row["Finish Date"],"dd.MM.yyyy") AS "Date"
sort Date ASC

it sort by dd .
Is there some way to deal with it?

[holroy]

Just use sort on the original date

[avliubimov]

Unfortunatelly it does not work ((