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:

    LOAD RowNo() as Key,

      *;

    LOAD * Inline [

    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

    Capture.PNG


    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))


    Capture.PNG


    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))

     

    Capture.PNG

     

    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:

    LOAD RowNo() as Key,

      *;

    LOAD * Inline [

    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

    Capture.PNG

     

    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))

     

    Capture.PNG

     

    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))

     

    Capture.PNG

     

    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