topic Re: Date associated with Min(quantity) in QlikView

Date associated with Min(quantity)

Wed, 07 Dec 2011 14:45:25 GMT

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.

Re: Date associated with Min(quantity)

Miguel_Angel_Baeyens — Wed, 07 Dec 2011 15:01:44 GMT

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

Re: Date associated with Min(quantity)

Wed, 07 Dec 2011 15:09:43 GMT

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

Really appreciate it!

Best regards. Tony