slice with two max rows or lastest rows

AppSheet AppSheet Q&A
,
1

Hi,

I am trying to make a slice to get the two latest rows for each people.

the table 1 is:

Name date start date end
john 01-02-21 30-02-21
peter 05-03-21 17-03-21
cara 01-02-21 30-02-21
peter 19-03-21 20-04-21
cara 02-03-21 15-03-21
john 01-03-21 11-03-21
john 15-03-21 02-04-21
john 03-04-21 07-04-21
peter 23-04-21 30-04-21

I need to get this slice (the two latest date end)

Name date start date end
peter 19-03-21 20-04-21
peter 23-04-21 30-04-21
john 15-03-21 02-04-21
john 03-04-21 07-04-21
cara 02-03-21 15-03-21
cara 01-02-21 30-02-21

I have tried to modify this expression, but I could not it

(
[_THISROW]
= MAXROW(
"Table 1",
"date end",
([_THISROW].[Name] = [Name])
)
2

I believe you mean *slice.

  1. Have another table People, that contains these columns:
  • “personID”, Key column
  • “Name”, Label column
  1. Your Table1 should contain:
  • “rowID”, Key column
  • “personID”, Ref column to People
  • remove the “Name” column from this table
  1. Add a new virtual column to People, named “latestRecords”, type List, with the following App Formula:

TOP(
ORDERBY(
FILTER(“table1”, [personID] = [_ThisRow].[personID]),
[date end], TRUE, [date start]
), 2
)

  1. Your Table1 slice filter condition should be:

IN([rowID], [personID].[latestRecords])

4 Likes
3

yes! slice

Thank you! @Joseph_Seddik

2 Likes
4

You went the virtual column route!

5

This is what I could think of, but @graham_howe crafter a better one.