Subtract last calculated value

[Gustavo_Eduardo]

Hello everyone. Good afternoon. I ask you a question. How can I do to use a value of the last loaded date in a list. I have a list of values ​​which I charge day by day. I am making progress. If I start a job today, there is no progress, but if I log a value tomorrow, I need to subtract yesterday's progress. Is there any way to take the value of the last registered row?

[Joseph_Seddik]

Hola Gustavo,

What columns do you have?

[Gustavo_Eduardo]

Hi Jose, good morning! I have a table with the following columns:

As you can see, the first record does not have a "Yesterday", therefore it should be 0, the rest if they have "yesterdays".

The idea is that the table can calculate "how much I need" and "how much I'm doing" on the current date. I appreciate your response

[Joseph_Seddik]

Since you have only one record per day, you don't need to go for more elaborate solutions to look for the last record. You already know the last record and you just need to dereference its corresponding desired column. Here's what you can do for your shown table:

1. Create a "Yesterday" column, its formula should be: [Registration Date] - 1
   
   
2. Yesterday's progress will be: [Yesterday].[Daily progress], which now you can use in calculating the Cumulative Advance and Remaining Quantity.

[Gustavo_Eduardo]

Great!, would this also work discounting the weekends? It may happen that the last date is a Friday and I am writing down a Monday, understanding that Saturdays and Sundays would not work.

[Joseph_Seddik · Accepted solution]

You should instead then use: WORKDAY([Registration Date], -1) 

[Gustavo_Eduardo]

Thank you very much jose!!! Greetings to you

[Joseph_Seddik]

Hola Gus,

Something came to my mind. The dereferencing will work only if your date column is the key column. If not, then you should instead add a “Yesterday key” column instead of “Yesterday”. Its formula should be:

SELECT(table[keyID],
   [Registration Date] =
   WORKDAY([_ThisRow].[Registration Date], -1)
)

An even simpler solution that will work only if you are sure the days will be added in order, is to use:

INDEX(table[keyID],
   COUNT(table[keyID])
)

@Gustavo_Eduardo 

[Gustavo_Eduardo]

Good Jose! I'm going to try right now to see how I can shape it. First I'll make sure that records by date can only be put one per day per element and no more than one.

Then I will try to find a way to do it following your advice.
As soon as I get stuck I'll tell you the progress

[rakhie_urs]

MAX() Should help

[Gustavo_Eduardo]

hi! good morning,

The expression is valid but its result type 'Date' is not one of the expected types: Number

[rakhie_urs]

How do I get a column value from the last row of the spreadsheet?

LOOKUP(

  MAX(My Table[_ROWNUMBER]),

  "My Table",

  "_ROWNUMBER",

  "Wanted Column"

)

Replace My Table with the name of the table from which you want the column value; and Wanted Column with the name of the column whose value you want.

[TeeSee1]

This one should help you.

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Find-latest-row-that-matches-these-criteria/m-p...

There are many other posts related to this.

You can search them with a search term like "AppSheet get the last row", etc..

[Gustavo_Eduardo]

Thank you @TeeSee1. I'll try this, have a nice day