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
I am trying to calculate mileage for the previous month.
I started by creating this dataview:
TABLE date AS Date, caseID AS Case, flat(list(startAddress, startCity, startState, startZip)) as Start, flat(list(startAddress,startCity,startState,startZip)) as End, odometerStart as “Odometer Start”, odometerEnd as “Odometer End”, (odometerEnd - odometerStart) AS Miles
FROM “CASA Mileage”
WHERE file.name != “CASA Mileage”
WHERE file.day.month = this.file.cday.month - 1
It works fine for displaying each mileage entry for last month, and creates a new column named Miles at the end that calculates the mileage for each trip.
The note needs to display this AND a total calculation off of that new Miles column.
This is what I tried, but I get no values.
TABLE WITHOUT ID Total
WHERE odometerEnd
WHERE odometerStart
WHERE file.cdate <= date(today)-dur(1 month)
GROUP BY “”
FLATTEN sum((rows.odometerEnd) - (rows.odometerStart)) AS Miles
FLATTEN sum(rows.Miles) as Total
You’re trying to reference a calculated column (Miles) that exists only in the view of your first table, not in the underlying metadata. rows.Miles doesn’t work unless Miles is an actual property or calculated during grouping.
You must re-use the (odometerEnd - odometerStart) calculation directly, because Miles is not a real field.
On the phone, so can’t test it, but can you try this:
```dataview
TABLE sum(odometerEnd - odometerStart) AS "Total Miles"
FROM "CASA Mileage"
WHERE file.name != "CASA Mileage"
WHERE file.day.month = date(today).month - 1 AND file.day.year = date(today).year
```
This does what I had before, but in a more elegant way. It gets a total of each line item (the miles traveled in each trip).
I need to take it a step further. I need to not only show each line item total, but then also sum up the “total miles” column.
I tried doing this:
TABLE Total
FROM “CASA Mileage”
WHERE file.name != “CASA Mileage”
WHERE file.day.month = date(today).month - 1 AND file.day.year = date(today).year
FLATTEN sum(rows.Miles) as Total
Group by “”
But got blank results.
How do I refer to the total miles to create a single calculated value of all miles driven?
You could get each trip’s distance first, then group, then sum the trip distances. It would be like:
```dataview
TABLE WITHOUT ID sum(rows.Miles) AS Total
FROM "CASA Mileage"
WHERE odometerEnd AND odometerStart AND file.cdate <= date(today)-dur(1 month)
FLATTEN odometerEnd - odometerStart AS Miles
GROUP BY ""
```
In a pure DQL table you can’t sum across the lines in the table. You can for each line sum stuff from each given file, but not from all the files.
This might change if I ever get around to it, but I’ve been so swamped lately with real life stuff, that it’s not looking very good for the near future.
A very good option using Dataview is to encapsulate your query displaying the various values, within some boilerplate dataviewjs to calculate a line with the total. This approach is show cased in the thread below:
