Calculating between two rows // Water Metering App

[Rodrigo_Mendez]

Hello. I’m making a Water metering app for my home, but I don’t know how to substract two rows in a column in order to get a result. Let me explain:

Water meters readings are aggregated numbers (like the odometer of your car). For example: If reading in day 1 = 1000 liters and reading in day 2 = 1100 liters, then your water consumption is 100 liters in day 1.

I have a table with the daily reading logs , but I don’t know how to calculate in between rows in order to get the consumption.

Furthermore, my home has 5 water meters in total. I would like to be able to take the daily readings of all of the meters and know the consumption.

Thanks in advance.

[Uzo]

do you need it for each row or the last two? for each infinite rows… you need to do it in google sheets and create an arrayformula, in the Glide table you need to create a separate column for each row and use a single value for each row…
or you can create an offset column, that will copy meter readings and move up by one row now you can subtract these two

[gvalero]

Hola,

This can give you an idea how you do it Running Totals

Saludos!

[ThinhDinh]

If you want to calculate each day’s consumption then it’s a bit different than the solution I gave in the link above:

Here are the steps to do it exclusively within Glide.

• Add a rowID column.

• Add an IDs array: lookup all rowIDs within this table.

• Add an index column, use the plugin Array > Find Element Index.

• At this step, what you do next is create a math column to calculate the next day’s index. The formula will be day + 1, with day being the current row’s index.

E.g: Day 0 will have the next day being 1, day 1 - next day being 2 and so on.

• Create a relation from the “next day” column back to the “index” column. You will have a relation to the “next day” for each row.

• Create a lookup on top of that relation to get back the “next day”'s water meter level.

• Finally, create a math column to calculate the consumption level. Next day’s level minus current day’s level.

It might be a bit different if you plan to have this app for multiple users (specifically on the relation part), but this should be enough for you to start.

[gvalero]

Well, I didn’t tell him that it was the solution, it was just an idea how to start!

[Rodrigo_Mendez]

Great idea. Will definitely try this. Thanks!

[gvalero]

Hola de nuevo!

Because I suppose that what @Rodrigo_Mendez has is a kind of data logger and sends the timestamp and the level reading at the same time to be saved, we can take the timestamp column as the RowID and save columns/data.
Also, I don’t like to use relations to the same table (it’s just my point of view) and more, if the table will grow and have many many rows.

Anyway, here is my newest solution for this case to make it simpler. This will look like this (again… assuming that a Timestamp value is sent at the same time!)

The steps are:

1. Create 2 new columns to have:
   a) all timestamps as an Array via Lookup
   b) all level readings as a List via Join List

2. Create an Index column using plugin Array → Find Element Index and set the parameters shown in my screenshot. This will work as a helper column

3. Finally, copy this small code and use it in JavaScript plugin to have the consumption per day. Don’t forget its parameters please!

…

var level=p1.split("|").map(Number);
var maxItems=level.length -1;
 
if (p2 == 0)
   return level[0];
else
   if (maxItems != p2)
      return level[p2] -  level[p2-1]  
   else
      return "0"

If everything was done correctly, your screen should look like this:

I hope this helps you!

Feliz noche Rodrigo!

[nathanaelb]

@Rodrigo_Mendez , I would do it exactly verbatim as Thinh suggested: a table with your readings (fields: Date, WaterReading), bring the data of the next row back up one row with a relation to be able to compare readings, a math column to calculate consumption.

I appreciate the skill displayed by gvalero and Uzo using javascript and formulas in Google Sheets, but to me that’s like going out to the restaurant and being asked to go to the kitchen to cook my own meal. Especially javascript.

Screenshots of what I would build

Data

Display

Filter out the last day

[gvalero]

Hola @nathanaelb

Here we are going to have a nice and interesting discussion: the Efficiency vs. Effectiveness and with Glide, we need to save rows, data and now, avoid unnecessary edits so, we need to be more efficient than effective.

And we have another point: the No-Code vs Low-Code movement

As anyone can see, both solutions give a valid result, one is a native No-Code solution and the other is a Low-Code solution because uses a small JS code

The problem to solve here is: which one is better (depending on developer’s skill)?
… but does “better” mean to be efficient or effective? That is the dilemma!

My solution (the Low-Code one) uses only 4 additional columns to get results …

… but the other one (No-Code) needs to create 8 columns instead… 2x the needed columns!. It can be effective but not efficient, we are wasting data and overloading the APP.

Imagine that the @Rodrigo_Mendez’s datalogger has 5k+ rows, the No-Code solution will have to load 20k extra values (4 columns x 5k) to show only the water consumption per day.

Worse yet, Rodrigo stated earlier that he “has 5 water meters in total” so, if the has 5 tables (1 per each water meter) and uses the No-Code solution, his APP will have to load and work with 100k extra values (5 tables x 4 columns x 5k values).
No way, there is no way I can support this kind of solution, we have to be efficient again mo matter if a small JS code is implemented.

I understand the cases and love for No-Code tools and ideas but sometimes and I think it’s a good example, a Low-Code solution in Glide can give you a better maintenance, makes your APP simpler and lighter and improves your APP performance up to 30-40%.

Note: Thinh my friend, nothing wrong with you or your idea, you are a crack , it’s just my point of view regarding to use of Low-Code solutions as plan B!!

Have a great day everyone!

Saludos!

[nathanaelb]

This makes a lot of sense, especially if one knows ahead of time that the scope of the project might reach or exceed certain limits of the tool.

It’s neat how you quickly determine computational scope, I’ll keep that in mind.

[Uzo]

the most economical solution is to create an extra sheet in google, copy readings to one column, offset by one row in the second column, subtract these two, and copy the result to the original sheet…
this way you don’t have any extra columns, no extra rows, and no extra computing… no coding… just simple formula ={A:A}

[gvalero]

Right!

Nothing can beat that idea to save as much data (columns) as possible!

Sometimes the best solution is in Google Sheet.

Thanks