Find a value from closest date in the same dataset

AppSheet AppSheet Q&A
,
1

I have a dataset with sell reports. In this report I have this values:

“Available cash from yesterday” (Virtual column)

“Sells in cash”

“Pays in cash”

“Available in cash” (Virtual column)

In “Available cash from yesterday” I’m trying to get the value “Available in cash” from the last report but I keep geting this error:

“Unable to fetch app definition. Error: Timed out waiting for a resource. The reason is usually a very large/slow table or a cross-table cycle in AppSheet formula”

Right now tha dataset has few rows so I assume is a cross-table cycle error but I don’t have any idea of how to solve it. Please help.

1 Like
2

What is the expression you are using in [Available cash from yesterday]? And what expression in [Available in cash]?

3

For the [Available cash from yesterday] I have tried two ways.

Make a virtual column named “Most recent report” with the formula

MAXROW("Sells report","Date", [Date] < [_THISROW].[Date])

Then, in the virtual column [Available cash from yesterday] use:

[Most recent report].[Available cash]

In the virtual column [Available cash from yesterday] I used the expression:

ANY(
  SELECT(
    Sells report[Available cash],
    (
      [Date]
      = MIN(
        SELECT(
          Sells report[Date],
          ([Date] <= [_THISROW].[Date])
        )
      )
    )
  )
)

For [Available in cash] I’m using:

[Available in cash from yesterday] + [Sells in cash] - [Pays in cash]
4

Do you have an infinite loop since both Virtual Columns are referencing each other?

[Available cash from yesterday] uses [Available in cash] in both formulas you shared

[Available in cash] uses [Available cash from yesterday]

Or are these different columns on different tables but just with the same name?

5

They refer each other in the same table.

I need them both to be virtual columns because I have another table called “Pays” so, when someone register a pay (sometimes from a previous day), the virtual column [Pays in cash] updates and this must update the [Available in cash] in that report, so this changes the [The available cash from yesterday] in the report from the next day, and so on in each report.

6

If you don’t mind sending screenshots of all your table’s data structures, I will see if I can offer a solution

7

The thing is that they are in spanish, but I can share them if it helps.

Sells report virtual columns.png
Sells report virtual columns.png852×391 36 KB

  1. “Pays in cash”
SUM(SELECT(Pagos[Total],AND([Fecha de pago]=[_THISROW].[Fecha de reporte],[Metodo de pago]="Efectivo")))+SUM(SELECT(Nomina[Nt + SE],AND([Fecha de pago de nomina]=[_THISROW].[Fecha de reporte],[Metodo de pago]="Efectivo")))
  1. Basically “Pays in cash” but for a view in the report.
SELECT(Pagos[Etiqueta de pago],
AND([Sucursal]=[_THISROW].[Sucursal],
[Fecha de pago]=[_THISROW].[Fecha de reporte]),
TRUE)

  1. “Month o the report” Organization purposes only.

  2. “Payroll”

  3. “Available in cash”

[Disponible en efectivo del día anterior]+[Total de ingresos en efectivo]-[Pagos en efectivo registrados]

  1. “Available in cash from yesterday” right now without formula to avoid app crash.

  2. “Monst recent report”

REF_ROWS("Reporte diario de caja", "Reporte más reciente")

Hope this helps to clarify the case. I appreciate the help.

8

Primero, haz todo lo posible por evitar el uso de columnas virtuales.

Mientras más columnas peor… y sobre todo si usas SELECT() y sus variantes! (FILTER, MAXROW, MINROW, ETC)

Te sugiero indicarnos todas las columnas que tienes y las relaciones entre sus tablas sin indicar las soluciones que tú planteas para evitar el ruido y proponerte una solución limpia

9

I will try to present the problem in a simple way to try to evade the noise of how I try to solve it.

I have 2 tables, “Sells report” and “Pays” with the following structure.

“Sells report”

  • [Report ID]
  • [Date]
  • [Available cash from yesterday] Virtual column
  • [Pays in cash] Virtual column
  • [Sells in cash]
  • [Available Cash]

“Pays”

  • [Pay ID]
  • [Date]
  • [Concept]
  • [Amount]
  • [Pay method]

This app is for a sells report in a restaurant. The idea is that the person in charge registers the sells report. The sells report must tell me how much cash is available in the restaurant. The cash available depends on how much cash sells the restaurant had and what we pay in cash.

Sometimes, because of the team structure and process, we register the pays days later, this affects the virtual column [Pays in cash] from the table “Sells report” and therefore the virtual column [Available in cash].

Parallel to the virtual columns I have normal columns. The idea behing of having this parallel is to keep track of the theoric amount of cash and the amount of phisical cash. Something like it is used with inventories.

10
Re: FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), R… - Google Cloud Community

Did you try this solution to get the data you wanted as mentionedin the OP?