0

PQL lookup second largest value

Hello, 

I am trying to write a PQL function which will find the row with the second largest value in a particular column of my matrix, is there a way to do this ? 

  Thanks,

Katie

3 replies

null
    • Lead Consultant Advanced Analytics
    • Michael_Daun
    • 3 days ago
    • Reported - view

    Hi Katie,
    I think it's easiest if you design the grid that serves as the basis for your dynamic texts (?) in such a way that the second largest value is always in the same position, i.e. if you sort the columns accordingly. This would allow you to access the label via label (1, 0, 1).

    If such sorting is not initially possible because you also want to display the grid itself in your dashboard (the grid in your screenshot looks quite nicely designed, so I assume that it is not only intended to serve as a data supplier for your PQL 馃槈), then I would make corresponding copies of this grid, which would then only serve as a PQL data supplier.

    And if you really do want to use PQL, then hopefully my solution will deter you accordingly. I proceeded as follows:

    1. Determine the row index of the largest value in the column
    2. Construction of two sub-columns, consisting of the values before the maximum value (0 to index-1) and the values after the maximum value (index+1 to 6, which is my max-index of rows)
    3. Determine in which of the two sub-columns the larger maximum is - this is then the second largest value overall.
    4. Finally, determine the label of the largest value for this sub-column

    The final PQL:

    if (
         data(IndexOfMax(DataSetColumnRange(1, 0, IndexOfMax(DataSetColumnRange(1, 0, 6))-1)), 1)
         >=
         data(IndexOfMax(DataSetColumnRange(1, IndexOfMax(DataSetColumnRange(1, 0, 6))+1, 6))+IndexOfMax(DataSetColumnRange(1, 0, 6))+1, 1),
    
        label(1, 0, IndexOfMax(DataSetColumnRange(1, 0, IndexOfMax(DataSetColumnRange(1, 0, 6))-1))),
        label(1, 0, IndexOfMax(DataSetColumnRange(1, IndexOfMax(DataSetColumnRange(1, 0, 6))+1, 6))+IndexOfMax(DataSetColumnRange(1, 0, 6))+1)
    )

    Simple, isn't it? 馃お There are perhaps, hopefully, simpler solutions. In my solution, it should be considered/improved that "6" was the highest index of my sample data (in your screenshot probably 14), this should be better designed dynamically, eg. via "NumberOfRows".

    Best regards,
    Michael
     

    • NPANS
    • 3 days ago
    • Reported - view

    's original suggestion is the right way to go. In fact, you can build a mini grid that specifically answers the question without repeating the entire query as is. Since you have powerful sorting and filtering tools in semantic PQL and query layers, I would build a 1 or 2 row grid that simply exposes the result(s) you need for dynamic text. With such a small, focused result, you can easily pint-point the values you are after and ensure they will continue to appear in that position.

    The extra query overhead should be negligible and it will be far easier to maintain.

    • Katie_Easton
    • yesterday
    • Reported - view

    Thanks both!

Content aside

  • Status Answered
  • yesterdayLast active
  • 3Replies
  • 19Views
  • 3 Following