Find a value in a table based on a date

AppSheet AppSheet Q&A
1

Based on a date, I must search a table for a value from another column, but the date is not necessarily exact, it can be within a range or be greater than the last one in the table.

For example

VUT - Table

|Since |Value|
| 19/02/2014 | 127 |
| 25/02/2015 | 150 |
| 11/02/2016 | 177 |
| 25/02/2017 | 300 |

Depending on the date of a transaction, I must take the value of the “Value” field and perform a calculation, but the date of the transaction can be anyone and not necessarily the one specified in the table, each record establishes a range and must take the "Value "depending on where you are

I would appreciate your comments.

2

Frank_Gonzalez:

Depending on the date of a transaction, I must take the value of the “Value” field and perform a calculation,

What is that calculation?

Frank_Gonzalez:

but the date of the transaction can be anyone …

Anyone?

Frank_Gonzalez:

… and not necessarily the one specified in the table

Then how do you know what date to use?

Frank_Gonzalez:

each record establishes a range …

How?

Frank_Gonzalez:

… and must take the "Value "depending on where you are

This makes no sense at all.

The problem you’re trying to solve is entirely unclear.

1 Like
3

Thanks.

I’ve to search in the table with a date variable.

the date must be greater than any … but it must not be greater than the next … if it exists

If I look for the date 30/06/2016 the result should be 177

If I search 30/3/2017 the result should be 300

if i search a date before the first Since Date Value … it have to give an error

The dates establishes the limit of a range of dates … and Value is the result expected

Thanks for your time

1 Like
4

So, given a date, find the table row with the closest lower date and return the value for that row?

ANY(
  SELECT(
    VUT[Value],
    (
      [Since]
      = MIN(
        SELECT(
          VUT[Since],
          ([Since] <= [_THISROW].[Date])
        )
      )
    )
  )
)
5

Something likes … i will try with your code … thanks a lot

1 Like
6

Have to change MIX to MAX on the SELECT … the list for this SELECT takes the rows where Dates ar greater and equal to Since … but the closest Since is the MAX

1 Like
7

Hello Steve,
This is my first post in this community.
I have a similar problem.
Here is my table:
Table Name: MilkPrice

|Price Date|Price per litre|

|10/10/2021|₹43.00|
|10/15/2021|₹45.00|

Based on your recommendation, i tried the following formula for fetching the Milk price as on date entered in another table.
ANY(
SELECT(
MilkPrice[Price per litre],
(
[Price Date]
= MIN(
SELECT(
MilkPrice[Price Date],
([Price Date] <= [_THISROW].[Date])
)
)
)
)
)

However, it gives me the following error message: “Arithmetic expression ‘([Price Date] <= [_THISROW].[Date])’ does not have valid input types” (Please see the link for screenshot: appsheet error.png - Google Drive)

Could you please help me to correct it?
Thanks a lot in advance.