PQL lookup second largest value

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
 

Michael Daun '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.