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)
Can you help me? Please
If you need a quicker response, please contact support@appsheet.com directly.
I sent an e-mail to the relevant address. No return yet
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
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.
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โ?
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
User | Count |
---|---|
44 | |
34 | |
27 | |
23 | |
15 |