Expression no result

reggieneo · 12-29-2020 08:29 PM

Hi All,
In test this is giving me the correct result but remain blank on actual: Both tested in Initial Value and app formula.

ANY(
SELECT(
ORDER DETAILS[DIPSTICK CLOSING],[TIMESTAMP]=MAX(SELECT(ORDER DETAILS[Timestamp],
AND([New Delivery]=FALSE,[Month]=[_Thisrow].[Month],[PRODUCT NAME]=“DIESEL”)
)),
)
)
+
ANY(
SELECT(
ORDER DETAILS[DIPSTICK CLOSING],[TIMESTAMP]=MAX(SELECT(ORDER DETAILS[Timestamp],
AND([New Delivery]=FALSE,[Month]=[_Thisrow].[Month],[PRODUCT NAME]=“PREMIUM”)
)),
)
)
+
ANY(
SELECT(
ORDER DETAILS[DIPSTICK CLOSING],[TIMESTAMP]=MAX(SELECT(ORDER DETAILS[Timestamp],
AND([New Delivery]=FALSE,[Month]=[_Thisrow].[Month],[PRODUCT NAME]=“UNLEADED”)
)),
)
)

please assist.
thanks

Marc_Dillon

Please elaborate on your situation here. Can you provide some screenshots? Is this a real or virtual column?

reggieneo

Hi Marc,
Not VC.
it works. I guess the select expression is taking too much time to give the result.
I also removed one “AND” criteria - “[New Delivery]=FALSE”.
Not the first time I noticed this situation.
to avoid dragging the sync time this is the only column i put app formula, the rest are all in spreadsheet.

Marc_Dillon

So your problem is solved?

No, that’s not a thing.

Expressions in real columns do not affect sync times.

reggieneo

why do you think it is taking too much time or sometimes not giving result? i haven’t done much changed though.
anyways. thanks

Marc_Dillon

Without seeing more details of the situation, I cannot guess as to what you are experiencing.

reggieneo

since its working now, I’ll keep an eye on it and update with details if occurs again.

Steve

Remove these pointless commas:

Steve

Here’s your expression, reformatted from my clarity (and without the extra commas):

ANY(
  SELECT(
    ORDER DETAILS[DIPSTICK CLOSING],
    (
      [TIMESTAMP]
      = MAX(
        SELECT(
          ORDER DETAILS[Timestamp],
          AND(
            ([New Delivery] = FALSE),
            ([Month] = [_Thisrow].[Month]),
            ([PRODUCT NAME] = “DIESEL”)
          )
        )
      )
    )
  )
)
+ ANY(
  SELECT(
    ORDER DETAILS[DIPSTICK CLOSING],
    (
      [TIMESTAMP]
      = MAX(
        SELECT(
          ORDER DETAILS[Timestamp],
          AND(
            ([New Delivery] = FALSE),
            ([Month] = [_Thisrow].[Month]),
            ([PRODUCT NAME] = “PREMIUM”)
          )
        )
      )
    )
  )
)
+ ANY(
  SELECT(
    ORDER DETAILS[DIPSTICK CLOSING],
    (
      [TIMESTAMP]
      = MAX(
        SELECT(
          ORDER DETAILS[Timestamp],
          AND(
            ([New Delivery] = FALSE),
            ([Month] = [_Thisrow].[Month]),
            ([PRODUCT NAME] = “UNLEADED”)
          )
        )
      )
    )
  )
)

This is potentially very expensive expression! You’re scanning the ORDER DETAILS table many, many times in the course of just one run of this expression.

reggieneo

thanks, Steve. totally agree that is very expensive. I need it but don’t know any other way.

MultiTech

Brute force always works! haha

__
I imagine the use of references and slices would significantly reduce the load on the device.