Date associated with Min(quantity)

[#1]

Hi I have created a simple table with data:

Machine

Date

Quantity

I would like to display the minimum and maximum quantities achieved on a machine along with the date they were achieved.

So something like -  'Minimum Quantity ' & [Quantity] & ' Achieved on '  then the Date.

How do I reference the Date associated with the minimum and maximum quantities ?

Thanks in advance.

[Miguel_Angel_Baeyens · Accepted solution]

Hi Tony,

Assuming the following script:

Data:
LOAD * INLINE [
Machine, Date, Quantity
A, 01/01/2011, 200
B, 01/04/2011, 100
A, 01/03/2011, 300
];

And using the following expression in a new text object

='Mininum Qty is ' & Min(Quantity) & ' and was achieved on ' & FirstSortedValue(Date, Quantity)

will return as expected. Similarly with the maximum quantity:

='Maxinum Qty is ' & Max(Quantity) & ' and was achieved on ' & FirstSortedValue(Date, -Quantity)

Note in this case how the FirstSortedValue() gets two parameters, the field to return (Date) and which value to sort by (Quantity desceding, hence the "-").

Hope that helps.

Miguel

[#3]

Many Thanks Miguel for your correct solution and for the fast response.

Really appreciate it!

Best regards. Tony