Dataview timelines overview GROUP BY issues

[lipilli]

Hi,

I want to track how many times I turn up on time for appointments. For this, I first annotate appointments with “trackOnTime::0/1” (1 for on time; 0 for late) in my daily notes. Then I use the dataview query below to get the total number of appointments (length(trackOnTime)) and the number of times I was on time (sum(trackOnTime)). I am only interested in the daily notes from a specific week. I am deriving the week from the filename and end up with the table in image 1.

However, what I want is a summary of my timeliness per week and not per day of a week. I tried GROUP BY week and FLATTEN trackOnTime to achieve this (second code block below), but the result is always an empty table (image 2).

Any ideas on how to achieve my goal?

Current Query for timeliness per day in a week:

TABLE round((sum(trackOnTime))/(length(trackOnTime),2)*100 AS "OnTime%",
length(trackOnTime) AS "No.Apptm"

FROM "Reviews"

WHERE date(substring(file.name,0,10)) >= (date(substring(this.file.name,0,10))) AND
date(substring(file.name,0,10)) < (date(substring(this.file.name,0,10))+dur(1 week))

AND contains(file.name, "daily")

SORT date(substring(file.name,0,10))  ASC

Attempt to sumarise the total timliness in a week

Note: I just have the on time count and not percentage here for now, because dividing by the nuber of appointments trows an error, as the nuber of appointments is 0.

TABLE 

sum(onTime) as "onTimeCount", 
length(onTime) as "No.Apptm"

FROM "Reviews"

WHERE date(substring(file.name,0,10)) >= (date(substring(this.file.name,0,10))) AND
date(substring(file.name,0,10)) <(date(substring(this.file.name,0,10))+dur(1 week)) 
AND contains(file.name, "daily")

FLATTEN trackOnTime as onTime

GROUP BY date(substring(this.file.name,0,10)).weekyear as week

[lipilli]

I solved the issue:

To perform a calculation on a group of entries from several files, you need to

1. Flatten the data values you are interested in (basically segments each match value into a single table row rather than having one row for all matches in one file)
2. Group the data by the value you are interested in, in my case by week.
3. Perform calculations on the table rows.

TABLE 

round((sum(rows.onTime)) / (length(rows.onTime)),2)*100 as "On Time %" 

FROM "Reviews"

WHERE 
date(substring(this.file.name,0,10)).weekyear = date(substring(file.name,0,10)).weekyear
AND
date(substring(this.file.name,0,10)).year = date(substring(file.name,0,10)).year
AND contains(file.name, "daily")

FLATTEN trackOnTime as onTime
Group By date(substring(file.name,0,10)).weekyear as Week