Solved: Help with SORT

Martina_Hawkins · 04-06-2020 04:28 AM

Hi,
I am trying to create a slice to show list of Equipment with Expired Calibration tests.
I have a table which will list the
Calibration Request Ref(key),
Item Id,
Date Calibrated
Calibration Due Date (Expiry Date)
etc.
Each time item is calibrated, a new row will be added so always want to check the most recent entry.

I have this expression in the Slice. Getting Error :Sort has invalid inputs.
Is there easier way of creating this list.

IFS(
Month(Today())<>“1”,
AND(
Day([Calibration Due Date])<Day(Today()),
Month([Calibration Due Date])<=Month(Today()),
YEAR(TOP(SORT([Calibration Due Date]),1))<=Year(Today())
),

Month(Today())=“1”,
YEAR(TOP(SORT([Calibration Due Date]),1))<Year(Today())
)

Steve

To get your slice to display only the row for each Item Id with the latest Calibration Due Date, try this as the row filter expression (replace MyTable with the name of the table the slice is built upon):

(
  [_THISROW]
  = MAXROW(
    "MyTable",
    "Calibration Due Date",
    ([_THISROW].[Item Id] = [Item Id])
  )
)

To get your slice to display only the row for each Item Id with the latest Calibration Due Date only if the Calibration Due Date is due, try this:

(
  TODAY()
  >= MAX(
    SELECT(
      MyTable[Calibration Due Date],
      ([_THISROW].[Item Id] = [Item Id])
    )
  )
)

View solution in original post

Aleksi

So… do you want to show in a sliced view only Equipment’s last entry… meaning all Equipmets are shown only once?

Martina_Hawkins

yes, so if a piece of equipment has expiry date 5/4/19 and then 5/4/20, I only want to check the latest and not show 5/4/19

Aleksi

For example… MAXROW(“TableName”,“Date”,[Equipment]=[_THISROW].[Equipment])=[KeyColumn]

Martina_Hawkins

not sure where this fits into my expression above where I need to check the Calibration Due Date.

From list below I want to pull out only Calibration Ref 3 as its the only one Due (Expired)

Calibration Ref No	Item ID	Calibration Date	Calibration Due Date
1	CATA-008	05/03/2020	05/03/2021
2	ACC-0001	03/04/2018	03/04/2019
3	ACC-0001	03/04/2019	03/04/2020
5	TEST-002	10/03/2020	11/03/2021

Martina_Hawkins

From attached view I don’t want to show the first row with Expiry date 3/4/2019

Steve

To get your slice to display only the row for each Item Id with the latest Calibration Due Date, try this as the row filter expression (replace MyTable with the name of the table the slice is built upon):

(
  [_THISROW]
  = MAXROW(
    "MyTable",
    "Calibration Due Date",
    ([_THISROW].[Item Id] = [Item Id])
  )
)

To get your slice to display only the row for each Item Id with the latest Calibration Due Date only if the Calibration Due Date is due, try this:

(
  TODAY()
  >= MAX(
    SELECT(
      MyTable[Calibration Due Date],
      ([_THISROW].[Item Id] = [Item Id])
    )
  )
)

Martina_Hawkins

Thank you so much @Steve. That’s exactly what I needed. Your help is much appreciated

Martina_Hawkins

@Steve I need help with 1 more thing please.

I want to send an email notification when a Calibration has expired.
So I added a column Calibration Expired which I want to have either Expired or Not Expired (initial Value) and will be hidden. Shown for now so I can see value.

When this field changes it will trigger the workflow to send the email notification.
I thought I could use your formula above in the column App formula but I can’t get it to work.
It also seems to change back to a yes/no type from Text type. Even when it is Text type the value is False - see attachment,

Probably even better to have Expiring, using today -7 to notify of calibrations expiring in next 7 days.

Also, would an App formula constantly keep the Expired field updated or is an App formula only triggered when Calibration Due Date is first entered?

Steve

In your expression, replace this:

[Calibration Expired]="Expired"

with just this:

"Expired"

In your expression, replace this:

TODAY()

with this:

(TODAY() - 7)

An app formula of a normal (non-virtual) column is updated only when the row is updated, such as when edited in a form or by an action, and the result is saved to the spreadsheet. An app formula of a virtual column is updated each time the app syncs, and is not saved to the spreadsheet.

Martina_Hawkins

Thanks again for your help @Steve