Value Associated with Min/Max Value of Another Field (Front End Solution)

Hey Qlikers -

This is my first post, so forgive me for not being an expert, but I have seen many people struggling with the topic. This should be first in the series of document I plan to update on this topic.

This one shows two different methods for finding Max or Min based on Another Field:

• FirstSortedValue
• Max/Min function with If Statement

Script:

QUALIFY *;

T1:

*;

Product, Country, Date, Price

A, USA, 1/1/2015, 10

A, USA, 2/1/2015, 11

A, USA, 3/1/2015, 11

A, USA, 4/1/2015, 12

A, USA, 5/1/2015, 15

A, USA, 6/1/2015, 15

A, USA, 7/1/2015, 12

A, USA, 8/1/2015, 15

];

Data in a table box object

Goal: Find Price for the most recent date

Method 1 Expression: =FirstSortedValue(T1.Date, -T1.Date) & ': ' & FirstSortedValue(T1.Price, -T1.Date)

Method 2 Expression: =Date(Only(If(T1.Date = Max(TOTAL T1.Date), T1.Date))) & ': ' & Only(If(T1.Date = Max(TOTAL T1.Date), T1.Price))

Goal: Find Price for the oldest date

Method 1 Expression: =FirstSortedValue(T1.Date, T1.Date) & ': ' & FirstSortedValue(T1.Price, T1.Date)

Method 2 Expression: =Date(Only(If(T1.Date = Min(TOTAL T1.Date), T1.Date))) & ': ' & Only(If(T1.Date = Min(TOTAL T1.Date), T1.Price))

From the above two expression, I have personally developed a preference of using FirstSortedValue for finding the Max/Min value depending on another field. But there are scenarios where FirstSortedValue won't work. One such scenario is detailed out below:

Script:

QUALIFY *;

T2:

*;

Product, Country, Date, Price

A, USA, 1/1/2015, 10

A, USA, 2/1/2015, 11

A, USA, 3/1/2015, 11

A, USA, 4/1/2015, 12

A, USA, 5/1/2015, 15

A, USA, 6/1/2015, 15

A, USA, 7/1/2015, 12

A, USA, 8/1/2015, 15

A, USA, 1/1/2015, 10

A, USA, 2/1/2015, 11

A, USA, 3/1/2015, 11

A, USA, 4/1/2015, 12

A, USA, 5/1/2015, 15

A, USA, 6/1/2015, 15

A, USA, 7/1/2015, 12

A, USA, 8/1/2015, 15

];

Data in a table box object

Goal: Find Price for the most recent date

Method 1 Expression: =FirstSortedValue(T2.Date, -T2.Date) & ': ' & FirstSortedValue(T2.Price, -T2.Date)

Method 2 Expression: =Date(Only(If(T2.Date = Max(TOTAL T2.Date), T2.Date))) & ': ' & Only(If(T2.Date = Max(TOTAL T2.Date), T2.Price))

Goal: Find Price for the oldest date

Method 1 Expression: =FirstSortedValue(T2.Date, T2.Date) & ': ' & FirstSortedValue(T2.Price, T2.Date)

Method 2 Expression: =Date(Only(If(T2.Date = Min(TOTAL T2.Date), T2.Date))) & ': ' & Only(If(T2.Date = Min(TOTAL T2.Date), T2.Price))

So here when there are two values, even though they are the same value, FirstSortedValue is not able to offer a result where as Method 2 is giving the result we expect to see.

I hope this will help people.

Please friends feel free to give suggestion/feedback to make improvements to this document.

Best,

Sunny