Associated value to max() function

[#1]

Dear sirs,

I have a table like this

Date Type Customer Remarks
010101 Sales Exxon Inc Needs post sale
010201 Sales BP Inc. Send christmas card
010301 Finance Greek State Stop sales, didnt pay
010401 Production CocaCola Needs more hardware
010501 Sales CocaCola CEO is nice

What i need to do - via a set analysis expression - is to have the REMARKS value, which is related to the second-to-latest date.
In this case - 010401 is the second-to-latest date, so in this case the output must be "Needs more hardware"

I can NOT use selections, as this will screw up the rest of my application. There is already a selection in effect, and by the way the set-analysis formula needs to disregard the current selection.

What do I do?

Thx.

[boorgura]

If you just need that value in a variable or in a Text object, this might work for you:

Set a variable vMax2Date as :

=max({1} yourDate, 2)

=maxstring({1} if(yourDate = '$(vMax2Date)', Remarks)

Let me know if this works or if you have any questions.

[#3]

Thanks, but i dont think i can use variables, since max(Date) varies with the values in my dimension field.

In short, I have a straight table where the first dimension is my customer-name. For each customer there is a sales history in a different table, and i need to pick the sales-responsible from the latest and the second-to-latest order pr. customer...

Hope it makes sense...?

[#4 · Accepted solution]

Firstsortedvalue()