Getting value from date before this row

[CRS_R_Newell]

I am currently building a cash balance application. I am using a valid count from a previous day to use in a referenced table for calculation. This works fine with this:

ANY(
SELECT(
Cash Tally[Total],
AND(
([Total] > 0),
([Date] < [_ThisRow].[Date]),
([Location] = [_ThisRow].[Location])),
FALSE
)
)

But, if i go to add a new reference from an older date, it does not update values by date, only by row number it seems.

How can I have the value referenced be the most recent?

[Joseph_Seddik]

Instead of ANY() which will return a random value from the selected list, you could use: ORDERBY() and INDEX().

[CRS_R_Newell]

Thank you for that.

I am using this now, which works:

INDEX(
ORDERBY(
FILTER(
"Cash Tally",
AND(
([Date] < [_ThisRow].[Date]),
([Location] = [_ThisRow].[Location])),
),
[Date],
TRUE
),
1
)

But, it returns the row key column. How do I get it to return a specific column?

[Joseph_Seddik]

Use SELECT() instead of FILTER(). 

[CRS_R_Newell]

Scrubbing my brain. What am I missing here?

[Joseph_Seddik]

@CRS_R_Newell Can't say for sure. Possibly your SELECT() statement is not working. Test your expression without ORDERBY() and see what result SELECT() is giving. 

[SkrOYC]

Or apply the OrderBy to the data before Select(),I guess AppSheet can't know which dataset it is after you selected a field that's not the key

[CRS_R_Newell]

The select statement is working by itself. It produces the listing correctly with all values, just not in order by date descending.

I put the select expression its own VC, then try to orderby in another VC and still get the error.

[SkrOYC]

BTW, the ussage of Any() has always be weird to me (I don't use it).

I remember @Steve said that it returns the first item from a list.

The docs says that there is a difference if the list is "constructed" vs "generated":

https://help.appsheet.com/en/articles/2347632-any#return-value

[Steve]

I always use ANY(...) rather than INDEX(..., 1).

ANY() returns the first value in the list.

A "generated" list is one produced by FILTER(), SELECT(), or REF_ROWS(), which don't guarantee the return order of the results. Because the order is not guaranteed, using ANY() with them is not guaranteed to give the result you might be expecting--because you may be incorrectly expecting them to return results in a specific order.

A "constructed" list is one your expression assembles in a specific order, such that you have confidence that the first item is what you would expect it to be. Wrapping FILTER() with ORDERBY(), or SELECT() with SORT(), will construct an ordered list.

[Suvrutt_Gurjar]

Nicely explained @Steve 

[Joseph_Seddik]

@SkrOYC Thanks Oscar, I didn't take notice of this constructed vs generated distinction. 

For me, I find no practical use for ANY() except to remove the List type from a single-element list. For example when dereference is not possible and you have to use SELECT() to find a single value.

[Joseph_Seddik]

Thanks much @Steve. This is quite informative! 

[CRS_R_Newell · Accepted solution]

I seem to have acheived the desired sort goal with MAXROW()

MAXROW("Cash Tally", "Date", (AND([Date]<[_THISROW].[Date],[Location]=[_THISROW].[Location])))

and then another VC to extract the value i need:

[Maxrow cashtally].[Total]

Currently works!

EDIT: I should add that I realize my explanation of the goal may not have been the best. Thanks for all the help.