Naming Formula by Date Entered

[Pointer]

According to the date column in the example image, what is the virtual column formula with the following values (FIRST, BETWEEN, LASTPREVIOUS, LAST) in “NAME”?

NAME(Virtual Column)
DATE(Normal Column)
ID(Normal Column)
NO(Key Column)

[Pointer]

Can you help me? Please

[Steve]

If you need a quicker response, please contact support@appsheet.com directly.

[Pointer]

I sent an e-mail to the relevant address. No return yet

[Pointer]

IFS([Location]=“outside”,

SWITCH([date],
MIN(SELECT(table[date],AND([location]=“outside”, [ID]=[_THISROW].[ID]))), “FIRST”,
MAX(SELECT(table[date], AND([location]=“outside”, [ID]=[_THISROW].[ID]))), “LAST”,
INDEX(SORT(SELECT(table[date],AND([location]=“outside”,[ID]=[_THISROW].[ID]),TRUE)), 2),“LAST PREVİOUS”,
“BETWEEN”),

[location]=“factory”, “factory”
)

how can I fix this formula works fine but the second largest date works incorrectly?

thanks

[Steve]

In what way doesn’t the expression work? Does it not identify any LAST PREVIOUS? Does it identify the wrong LAST PREVIOUS?

On the surface, your expression looks fine. If, however, the Location column value might ever be blank, you may run afoul of an unfortunate and non-intuitive quirk of the is-equal-to (=) operator: if the left-side operand value is blank, the test will be TRUE regardless of the right-side operand value. To avoid this quirk, I would recommend rewriting every occurrence of:

([Location] = “outside”)

as:

(“outside” = [Location])

Try that and see if it makes a difference.

[Suvrutt_Gurjar]

In addition to useful input by @Steve, one thing you may wish to check and mention is, are there any IDs in you app just the way you have shown in the spreadsheet example you have given in the first post of this message thread.

In the example there seems to be error in listing “LAST PREVIOUS” and “BETWEEN” values for names A ? For name A, is 2013 “LAST PREVIOUS” rather than “BETWEEN”?

[Pointer]

IFS(“outside”=[Location],

SWITCH([date],
MIN(SELECT(table[date],AND([location]=“outside”, [ID]=[_THISROW].[ID]))), “FIRST”,
MAX(SELECT(table[date], AND([location]=“outside”, [ID]=[_THISROW].[ID]))), “LAST”,
INDEX(SORT(SELECT(table[date],[ID]=[_THISROW].[ID]),TRUE), 2),“LAST PREVİOUS”,
“BETWEEN”),

“factory”=[location], “factory”
)

It works normally this way, Thank you @Steve, @Suvrutt_Gurjar