Between a start date and the next closest date of ...

Jorge_Carreon · 07-13-2020 12:47 AM

Hello everyone, I’m trying filter data in a series of date that start from any given date to the next closest date. For example, in a set of dates of 7 july, 10 aug, 12 sept, 15 dec, I need to filter data only between the start date of 7 july and the next closest of 10 aug, (excluding data from 10 aug to 15 dec.) The dates variables, and are not hard coded.

In Sheets, I use something like a combination of Lookup, MAX and Filters:

=ArrayFormula(vlookup(MAX(if(FILTER($K$3:$P$36,$J$3:$J$36=B4)<=H4,FILTER($K$3:$P$36,$J$3:$J$36=B4))),FILTER($K$3:$P$36,$J$3:$J$36=B4),{3,4,5,6},0))

What’s the equivalent in Appsheet?

Any solution or clue will be greatly appreciated!

Jorge

Suvrutt_Gurjar

Please try if following filter helps

AND([Date]>=INDEX(SORT(TableName[Date],FALSE),1) , [Date]<=INDEX(SORT(TableName[Date],FALSE),2) )

Edit: Basically in sets of dates like of 7 july, 10 aug, 12 sept, 15 dec

INDEX(SORT(TableName[Date],FALSE),1) will give the earliest date ( 7th July) and
INDEX(SORT(TableName[Date],FALSE),2) will give next to earliest date (10 Aug)

You may wish to use it furher appropriately in exact filter expression you wish to have.

Jorge_Carreon

Hi Suvrutt, just to thank you for your response though I still have to try your formula. I’ll let you know in due time. Thanks again!

Steve

This should get you the first date from a given list of dates (date-list) that falls after a given start date (start-date; note that it occurs twice):

ANY(
  LIST(DATE(""))
  + SPLIT(
    INDEX(
      SPLIT(
        (
          LIST("")
          + UNIQUE(
            SORT(
              date-list
              + LIST(start-date)
            )
          )
          + LIST("")
        ),
        (" , " & start-date & " , ")
      ),
      2
    ),
    " , "
  )
  - LIST("")
)

UNIQUE(SORT(date-list + LIST(start-date))) constructs a list of dates from the original list (date-list) that includes the desired start date (start-date). The resulting list of dates is sorted in ascending order (from earliest to latest), then duplicate entries are removed.
LIST("") + ... + LIST("") adds a blank entry at both the beginning and end of the list of dates from (1) (...) to ensure (3) will work.
SPLIT(..., (" , " & start-date & " , ")) implicitly converts the list from (2) (...) to text, then splits it back into a list around the separator consisting of space-comma-space (" , "), the start date, and space-comma-space again. The result will be a list of two text values: the first will be dates before the start date, and the second will be dates after the start date.
INDEX(..., 2) extracts the second (2) of the two text values produced by (3) (...😞 the list of dates after the start date.
SPLIT(..., " , ") splits the text value from (4) (...) into a list of individual dates. Note that the result of a SPLIT() is a list of Text-type values, not Date-type values.
LIST(DATE("")) + ... - LIST("") constructs a new list of Date-type values, starting with a blank Date type value (DATE("")) and the list of dates from (5) (+ ...), then removes any blank values (- LIST("")).

A list’s type is determined by the type of its first item when the list is created. LIST(DATE("")) creates a blank value of type Date as the first item in the list, thereby making the list of type Date. Any other items added to the list are automatically converted to the list’s type.
ANY(...) extracts the first item from the resulting list from (6) (...), which should be the first date from the original date list that occurs after the original start date.

Jorge_Carreon

Hi Steve, I still have to try Suvrutt’s solution. In what way does your suggestion differ from his? Thanks for your help…

Between a start date and the next closest date of a series