Use value of previous record (lag) in a dataview column calculation

Help
1

What I’m trying to do

I want to write a dataview query to calculate fuel use of my car. For this I need the previous total_km of the car from the previous note. In other SQL dialects it could for example be done with a LAG function, e.g.:

TABLE total_km - lag(total_km)/Fuel_volume as "km/L", round(Cost/Fuel_volume, 3) as "€/L" From "Fueling" Sort Date

I figured this is not something that’s supported. I think I might see the following options:

  • the files are created using templator. Maybe add the previous total_km value automatically?
  • maybe run a dataview query to set a local :: parameter (if even possible) using a dataview query in every single file?
  • can I use some joining to fix this?

Things I have tried

Read the docs, googled and searched the forms…

Thanks in advance, I am curious what you guys can come up with!

2

I think the trick you need to use is to do GROUP BY true in conjunction with a WHERE-clause limiting your data set to previous fillings. Then you should be able to do sum(rows.Fuel_volume) and similar to calculate total fuel consumption, and work out the mileage and so on.

It’s hard to write a proper query, as I don’t know your exact structure and have any sample data, but the previous paragraph should get you going. Also note that you might want to use min(rows. ? ) to get initial mileage out of the set.

Try doing the group and where clauses in a table where you also use rows as a column to see what’s available to you for other functions.

3

Thanks for your advice!
A GROUP BY true will (in general sql) just create one group right, i.e. do nothing?

I see I forgot a bracket, and I think I wasn’t clear on what I am trying to achieve, so will try to nuance it a bit.
Data looks like this:

File total_km Fuel_volume Cost
tankbeurt_2023-05-24 184734 43.32 77.93
tankbeurt_2023-06-20 185464 43.87 78.92
tankbeurt_2023-09-12 188222 42 90.33
tankbeurt_2023-10-10 188893 40.87 84.15

To calculate the fuel efficiency of the last row, I need to calculate the difference of the distance (188893 - 188222). After that, its very simple to calculate distance/liter fuel, i.e. km/L

Ideally solved with dataview alone of course, but an alternative (option 1) could be to add the “previous mileage” using templator at note creation time to get:

File total_km prev_km Fuel_volume Cost
tankbeurt_2023-05-24 184734 null 43.32 77.93
tankbeurt_2023-06-20 185464 184734 43.87 78.92
tankbeurt_2023-09-12 188222 185464 42 90.33
tankbeurt_2023-10-10 188893 188222 40.87 84.15

If you understood me all along :smiley: could you write a rough example based on this information to clarify what you meant?