Automated Report selecting last 3 rows

[Travis1]

Hi All, 

I am working on a automated report that needs to send the last 3 cells of a sheet. I have found a formula with MAXROW that sends the last row, but I cannot find a way to make it do MAXROW (Minus 3). 

Can someone help me with this? I feel like this is a simple fix and I'm just too close to the project. 

Thank you in advance

[Travis1]

I may have solved it, but still open to other/better solutions. 

Step 1: Made a virtual column called "timecheck" with formula [_thisrow].[time]=MAX(sheet3[time])

Return value = Y/N

step 2: Report Filter set to "Y" for [timecheck]

[MultiTech]

As long as you put the items in the correct order:

TOP([Your_List_Here], 3)

https://support.google.com/appsheet/answer/10107704?hl=en

[Travis1]

thats an interesting solution, but one of the feature I am unable to change is that new rows are added to the bottom. If only there was a BOTTOM() .. hah 

[MultiTech]

That's what OrderBy() is for

https://support.google.com/appsheet/answer/10107362?hl=en

[jrullan]

Once you have found the last row with MaxRow, this formula should get the last three records (-3):

For example,

Filter(
  "Some_Table",
  [_RowNumber]>
  Index(Select(Some_Table[_RowNumber], [ID] = MaxRow("Some_Table","_RowNumber")),1)-3
)

This would return the last three (-3) records of Some_Table based on _RowNumber.

It basically translates to: "Once you have found the last record of Some_Table based on _RowNumber, then get that record's _RowNumber, substract 3 and Filter the table Some_Table using as condition that _RowNumber should be greater than the resulting number. In essence, the last 3 records.

[Travis1]

I like this - but it doesn't work in a report filter. I am working on turning this into a yes/no .. 

[jrullan]

@Travis1 ,

It's great that you found a way. I am a bit curious about what you are trying to accomplish. Whatever it may be, I'd probably try out @MultiTech solution in the future seeing that it's probably more efficient (using built in functionality over custom queries such as mine).

[MultiTech]

@jrullan Gotta start somewhere, and brute force is actually where I usually start if I'm unsure if something is possible. 😉 If the monster of a formula works, then I'll go about finding ways of making it more efficient.

And the solution I pointed out here, might not actually fit this situation.  As @jrullan alluded to, @Travis1 you never really gave us much insight as to what or where you were trying to accomplish this.

• Which actually plays a huge role in how you'll solve your problem.

[Travis1 · Accepted solution]

Okay, my previous solution did not work. 

Here is how I solved it: 

1. Created a slice with formula: [TIME]=MAX(Notifications[TIME])

2. had a report call the slice, Condition set to: "true"

NOTICE: All of my records have a timestamp on when they are moved from one sheet to another (External tool). By pulling the MAX time, I was able to ensure the most recent time was selected. 

Thank you for everyone's help! 

[GV]

Hi @Travis1 

Thanks for your message. I want to create a PDF report with all rows from a specific tab and send it by e-mail. I am a bit lost between functions : Start, Max etc..

Your example seems a bit more complex than mine, but I am a bit lot on where should I put all theses formulas.

Could you help me ? 🙂

[MultiTech]

First: Follow this link for the solution:  https://support.google.com/appsheet/answer/10107704?hl=en#:~:text=Rows%20with%20highest%20values

So... much... inefficiency.... in this thread....

----------------------------------------------------------------------------------------
A note on SELECT()
----------------------------------------------------------------------------------------

The select() statement is literally brute force - any time you use it, you're telling the system:

• Hey go to table X and get EVERY SINGLE RECORD in the table, search through them all, and find the ones I want.

If you've got something like:

Filter(
  "Some_Table",
  [_RowNumber]>
  Index(Select(Some_Table[_RowNumber], [ID] = MaxRow("Some_Table","_RowNumber")),1)-3
)

You're saying:

• For the 1st record of Some_Table:  (this is the start of the filter
• For the 1st record of Some_Table:  (this is the start of the SELECT()
  • Go grab every record in the Some_Table table  (this is the start of the MaxRow
  • Search through them all and give me the highest number based on _RowNumber
  • Now that I've got the highest row number, is this 1st record that row number?
  • No?  Okay... NEXT!!!!
    
    
• For the 2nd record of Some_Table:  (this is still the 1st of the filter, 2nd of the select
  • Go grab every record in the Some_Table table
  • Search through them all and give me the highest number based on _RowNumber
  • Now that I've got the highest row number, is this 2nd record that row number?
  • No?  Okay... NEXT!!!!
    
    
• For the 3rd record of Some_Table:  (this is still the 1st of the filter, 3nd of the select
  • Go grab every record in the Some_Table table
  • Search through them all and give me the highest number based on _RowNumber
  • Now that I've got the highest row number, is this 3rd record that row number?
  • No?  Okay... NEXT!!!!
    
    
• And on and on for EVERY... RECORD... IN THE TABLE.... just for the select!
• Then we move on to record number 2... for the filter statement
  • we go through that WHOOOOOOOLLLLLLLLE process again
  • Then again.... for record number 3
  • Then again... o.... m..... g..... 

You see why Chef Ramsay's head is about to explode? 😜

Nested SELECT() (which includes any of the ports as well) is a huge no-no

-----------------------------------------------------------------------------------------

Well what do I do then!??!??

TOP() allows you to pull out the top X values from a list

• TOP(LIST("Red", "Yellow", "Green"), 2) returns a list of 2 items: Red, Yellow

In the documentation for TOP, they actually answer the question of this post... exactly what you need to do is explained, with details for each step so you can understand what's happening at each stage.  

Follow this link for the solution:  https://support.google.com/appsheet/answer/10107704?hl=en#:~:text=Rows%20with%20highest%20values

[MultiTech]

TOP(
  OrderBy(
    Table[TableID], 
    [_RowNumber],
    true
  ),
  3
)

Or you could base things on that timestamp column you have... that's what I would do.

👍