Slice of matching reports

Fof_Webmaster · 11-22-2019 01:05 PM

I need to create a slice from my Reporting table where there are 2 or more matching [License Plate #] and those matching reports have [Datetime] that differ more than than 10 days.

I have scoured the community and tried many formulas but everything I do has failed.

Bob

Steve

Try:

  ISNOTBLANK(
    FILTER(
      "Reporting",
      AND(
        ([_THISROW].[License Plate #] = [License Plate #]),
        ((ABS(HOUR([_THISROW].[Datetime] - [Datetime])) / 24) > 10)
      )
    )
    - LIST([_THISROW])
  )

Fof_Webmaster

Steve,

This works great. Thanks for the quick reply! I added another AND expression to eliminate reports without a license plate number: ([_THISROW].[License Plate #] <> “”).

Can you clarify so I understand -

- LIST([_THISROW])…what does this do?
([_THISROW].[License Plate #] = [License Plate #])…I believe this is a dereference, but I don’t understand how it is working, since there is no current entry of a license plate #, ie [_THISROW].

Bob

Steve

FILTER("Reporting", ...) produces a list of rows from the Reporting table that match the given criteria (...). - LIST([_THISROW]) then removes the current row from that list. We’re let with rows other than the current one that match the criteria.

The slice filter expression is applied to each row in the table the slice is based on. _THISROW then refers to the row currently being considered by the slice filter. ([_THISROW].[License Plate #] = [License Plate #]) matches rows in the Reporting table with a License Plate # column value that matches the current row’s License Plate # column value.