Line graph data structure

[Nicolas_Feldman]

Hi everyone!

I´m trying to build a line graph (series), but I cant match the results I need.

So I have this table, generated from a form that measures water levels from different ponds at different time. (It´s an engaging community wildlife monitoring project, so anyone will be able to report at anytime on any WaterPond)

I need to build a line graph which has the water level (y) of the different ponds (series), over time (x).

I´ve tried using this data but it shows only one line (series) with all the events where waterlevel was measured, but it doesnt differentiate between WaterPonds.

I believe the way I structured the form wont allow me to do this, so I restructured the data on a new table on GSheets like this using vlookup:

One problem is solved, I have different Series for each waterpond.
The problem now is that each event carries a N/A or “0” result for the ponds that were not measured at that same time.
This means that for each date or event, I get 3 values: one correct number for the pond measured, and two zeros for those that weren´t measured
Meaning that the series drops to zero those days that the pond wasnt measured.

Im stuck here now, and Im pretty sure Im overthinking this and there´s an easy solution for this…
I hope someone has an idea that can help me unlock this thing.

Thanks for reading!

[Jonathan_S]

Do you want to build this in Appsheet? or Google Sheets?
You do seem to have a a lot of formulas in your gsheet.

[Nicolas_Feldman]

Hi Jonathan, thanks for your answer.

I´d rather do it on Appsheet, I just haven´t found the way for this chart in particular.

[Jonathan_S]

Sorry Man, All I can say is Im in the same boat.
I Tried to have the water activity of different production lines overlayed onto the same chart, this is as good as I could get it.
Hoping the solution to this thread will help me. Thousands of entries on this chart.

[Nicolas_Feldman]

Thanks @Jonathan_S
How did you manage to do that?
How are your tables structured?

What about slicing your data into smaller charts? Would that help decongest the chart?

[Suvrutt_Gurjar]

Hi @Nicolas_Feldman,

I am sure @Jonathan_S will have insights to share on your queries.

If I may add, as per my understanding you may either have

1. Three independent charts for each pond, so any days when readings are missing are eliminated by way of creating slices. You could then base one each chart for each pond based on those slices.

2. For a combined chart, when the reading is missing for a pond on a day, the chart line will naturally default to zero. So you could build some logic such as if the reading is missing on a day for a pond , the latest previous reading is considered in place of zero or #N/A . However I believe this logic will be somewhat complex and will include MAX() or MAXROW() etc. if implemented through AppSheet.

[Nicolas_Feldman]

Thank you @Suvrutt_Gurjar for your experienced insight.

Your option 1:

Three independent charts for each pond

Was easily done with REF_ROWS. No need for slices. I´ve had this since the beginning.

Your option 2:

build some logic such as if the reading is missing on a day for a pond, the latest previous reading is considered in place of zero or #N/A

I like this idea, will work on the formula and let you know the results.
I was thinking something like:

IF([WaterLevel]=“0”,ANY(
SELECT(
Reporte Aguadas[WaterLevel],
(
[_ROWNUMBER]
= MAX(
SELECT(
Reporte Aguadas[_ROWNUMBER],
([WaterPond] = [_THISROW].[WaterPond])
)
)
)
)
)

This brings back, the last value, even if it is a Zero…

What is the best way to accomplish this???:

the latest previous reading is considered in place of zero or #N/A

What would be the expression or formula to let AppSheet know I want the last Row but with Data?

BTW, credits to @Steve, from whom I learnt the use of that formula.

Fetch value(not key) from Last Row with condition Questions

Try: ANY( SELECT( Table B[WaterLevel], ( [_ROWNUMBER] = MAX( SELECT( Table B[_ROWNUMBER], ([Pond] = [_THISROW].[Pond]) ) ) ) ) ) SELECT(Table B[_ROWNUMBER], ([Pond] = [_THISROW].[Pond])) gets a list of row numbers for all rows in Table B wherein the Pond column value matches the Pond column value of this (Table A) row. MAX(...) gets the largest of the row numbers from (1). SELECT(Table B[WaterLevel], ([_ROWNUMBER] = …

[Suvrutt_Gurjar]

Hi @Nicolas_Feldman,

Nicolas_Feldman:

What would be the expression or formula to let AppSheet know I want the last Row but with Data?

I believe the expression to bring non zero value will be something like
IFS([WaterLevel]=“0”,ANY(
SELECT(
Reporte Aguadas[WaterLevel],

AND([WaterLevel] >0,
(
[_ROWNUMBER]
= MAX(
SELECT(
Reporte Aguadas[_ROWNUMBER],
([WaterPond] = [_THISROW].[WaterPond])
)
)
)
)
)
)
)

However, I believe the above expression will be in general a sync time expensive expression with a SELECT() within another SELECT().

You could possibly split the expression in two columns (one that finds the MAX row number and another that computes latest Water level based on max row number ) but in general, I believe as mentioned in the beginning, this will be overall longish implementation.