Re: How to get Average of the last three row value

111139 · 03-18-2021 11:04 PM

i want to make and Format view to warn me if the Average of the last three rownumber price value is over 100 $

what should be the expression for the virtual colume

Thank you very much

Steve

Probably best to use a regular (non-virtual) column to store the result of the test (the expression below), as a virtual column for this will greatly slow sync times. The format rule can just check this column’s value.

(
  AVERAGE(
    SELECT(
      table[price-column],
      IN(
        [_ROWNUMBER],
        TOP(
          SORT(
            SELECT(
              table[_ROWNUMBER],
              ([_ROWNUMMBER] < [_THISROW].[_ROWNUMBER])
            ),
            TRUE
          ),
          3
        )
      )
    )
  )
  > 100.0
)

See also:

reggieneo

Hi Steve,
How can I use this expression to get the last 3 values of a column?

I need to compare if the most recent 3 values are the same text “GY” from the [Shift] column.

thanks in advance.

Steve

SELECT(
  table[column],
  IN(
    [_ROWNUMBER],
    TOP(SORT(table[_ROWNUMBER], TRUE), 3)
  )
)

Replace table (2x) and column with the appropriate names.

I don’t understand what this means.

reggieneo

I need to compare if the most recent 3 values are the same text “GY” from the [Shift] column.

Hi,
I need to check if the last 3 consecutive values of column [shift] are all “GY” .
That is the reason I needed to count the last 3 values.

thanks

Steve

Hmmm… This is complicated. I would suggest adding a virtual column named (e.g.) Last 3 with the App formula:

TOP(
  ORDERBY(
    FILTER("table", TRUE),
    [_ROWNUMBER],
      TRUE
  ),
  3
)

That will give you the three most recent rows. Adjust the FILTER() subexpression as appropriate to limit the rows.

To check that the last three rows contain only GY in the Shift column:

ISBLANK([Last 3][Shift] - LIST("GY"))

reggieneo

Thanks, Steve,
I read that VC with this expression is too costly. so I have added a new column “[Shift Checker]” instead to check if indeed the last 3 rows of [Shift] Column is “GY,GY,GY”. It worked.
then when the last 3 rows is “GY,GY,GY”, I want the “[date]” column to add one day based on most [recent date] .

I tested my expression and it returns correct value e.g Jan 19 becomes Jan 20. but when I actually run the app, it didn’t add the date. why do you think is that?

expression:
“IF(and(Trim([Most Recent].[Shift Checker])=Trim(“GY , GY , GY”),(Today()-[Most Recent].[Date])=24),
1+[Most Recent].[Date],[Most Recent].[Date]
)”

Steve

Where are you using the expression?