Create slice with latest 2 dates

[Rolls_Royce]

I am new to Appsheet and trying to create a slice with the most recent 2 dates of a field called [date]

So the table is like this:
[ticker] [date] [value]
Ticker1 2021-01-02 10
Ticker 2 2021-01-02 11
Ticker1 2021-01-03 20
Ticker2 2021-01-03 15
Ticker1 2021-01-04 100
Ticker2 2021-01-04 120

The slice should return
Ticker1 2021-01-03 20
Ticker2 2021-01-03 15
Ticker1 2021-01-04 100
Ticker2 2021-01-04 120

It sounds very simple, but I can’t seem to find the right expression to make it work.

[Suvrutt_Gurjar]

Maybe the following slice filter expression

IN([Date], TOP(SORT(SELECT(Table Name[Date], [ticker]=[_THISROW].[ticker]),TRUE),2))

[MultiTech]

Suvrutt_Gurjar:

IN([Date], TOP(SORT(SELECT(Table Name[Date], [ticker]=[_THISROW].[ticker]),TRUE),2))

This is what I call “brute force” - and it works, because brute force always works. But it’s rarely the actual way you should actually go about it.

• Brute force is helpful to prove that something is possible, that the results will actually work if given the correct data.

The main issue here is that you’re running a SELECT() formula for each row in the table; which means you end up with an N^N number of calculations happening. (Which can get really crazy, really fast.)

---

I would fundamentally switch things around.

• Create a Parent level in your app - for the Ticker types
• Add a reference into your data entry table TO the ticker table
  • this will automatically group each ticker entry into groups for each ticker type inside a [Related TickerPrices] column (or whatever you called your data entry table)
• From here you’ll end up with a REF_ROW() on the Ticker table
  • Then you can pull out the TOP() two pretty easy:
    TOP([Related TickerTrackers], 2)
    • You may need to apply an ORDERBY() around the REF_ROW() to make sure things are in the correct order; fyi.
      OrderBy(REF_ROWS(...), [Date], true)

At this point you’ll then have the top two ticker prices for each ticker; since this lives on the ticker level (the parent), it can be extremely helpful to see things there and you might explore the possibilities you find at this point.

But to continue to your full-solution (a slice of ALL the top 2 ticker prices)

---

In your slice, change your formula to something like this:

in([TickerTrackerID], split(concatenate(Tickers[Ticker_Top_Two_Trackers]), " , ")

While this is still performing a SELECT() on each record of your tracker table - instead of searching through the TickerTracker table (with thousands of records) it’s searching through the Ticker table (with only a few).

---

I know this sounds like a lot of stuff to do, like… there’s a lot going on just to accomplish something that was “easily” accomplished with a formula.

• But if I’m interpreting your data correctly, you’re building a stock tracking app of some sort…
  • which means you’re going to end up with thousands (if not tens of thousands) of records in that ticker tracking table.
• And trying to run a brute force formula over a table with even 5k records will show you what I’m talking about (in regards to an effect on performance).