Select nth listbox value

[jzimolong]

I have a field with following values in a listbox:

20130630

20130531

20130430

20130331

20130228

20130131

20121231

........ Continues down

I simply need to determine, in a variable, the value within listbox that is 3 down from the MAX.

So in this case, it would be 20130331 (MAX is 20130630, then go 3 values down).

Thought this would be simple, but I can't get this.

[MK_QSL]

LIKE THIS?

[jzimolong]

I don't think so, it needs to be dynamic.  I added value 20130731 to your inline table, then reloaded.  The variable remains 20130331, but it should now be 20130430.

[MK_QSL]

NOW?

[jzimolong]

Interesting solution.  I'm trying to resolve this without having to add an ID field and extra scripting, but I hadn't considered doing that. 

If I can figure this out without adding to the load script, it would be helpful because I could then re-use the solution on different fields.  But this technique will also provide some value.

Still hoping to get this resolved without changing the load script and table.

[#6 · Accepted solution]

I used Manish's template but you can achieve the solution without running it in script.

please see my variable

[MK_QSL]

USE MAX FUNCTION AS suggested by Byron...

Create a variable...

Let vForthRecord = Max(VALUE,4);

max([{set_expression}][ distinct ] [ total [<fld {, fld}>]] expression [, rank]))

Returns the numeric maximum value of expression or field iterated over the chart dimension(s). Rank defaults to 1 which corresponds to the highest value. By specifying rank as 2 the second highest value will be returned. If rank is 3 the third highest value will be returned and so on.

Examples:

max( Sales )

max( Sales, 2 )

max( Price*Quantity )

max( total Sales )

max( {1} total Sales )

[#8]

If you have answer please share , it is helpful others

[jzimolong]

Thanks Byron and Manish.

I ultimately used the Max function to get this working:

=Max({1}PERIODID,4)

Needed the {1} so the variable would consider all values in PERIODID, irrespective of the current selections.

Joe