How to get second to last value?

[#1]

Hello,

I need to make comparisons between KPIs on the "Last Date" vs. the "Penultimate Date" of the dates the users selected. I have 2 variables to store these dates (vLastDate, vPenultimateDate).

For example the following dates are available in the dataset. The ones marked with asterisks are the ones the user has selected.

Dec

** Nov **

** Oct **

** Sep **

Aug

-----> vLastDate should be Nov, vPenultimateDate should be Oct

Dec

** Nov **

Oct

** Sep **

** Aug **

-----> vLastDate should be Nov, vPenultimateDate should be Sep

To get the last date I just use max(MyDate). But how can I get the previous date selected?

Thanks for your help!

Mara.

[Michiel_QV_Fan]

Are you using these varables in expressions?

[giakoum]

do you have a month number associated to month? If yes, you could just reference it as Max(MyDateID) - 1

[sunny_talwar · Accepted solution]

Not sure if I understand completely, but try this may be:

Max(MyDate, 2)

[#5]

Michiel, thanks for you reply. Yes I am using the variables in expressions, using set analysis.

[#6]

Ioannis,

Thanks for your reply. Yes, these are actual dates - I just used months here to simplify my example.

Unfortunately if you use max(MyDate)-1, you always get the penultimate date, regardless if it is within current selections or not. I need the penultimate date to be within the current selections. In my second example the user has selected Nov, Sep, Aug, and the penultimate month should be Sep (and if I do -1, as you suggested, I get Oct).

[#7]

Wow, so simple. Thanks sunindia!

[sunny_talwar]

No problem

I am glad I was able to help.

Best,

Sunny

[jorgett]

Hi Sunny_talwar,

I have tried this and it works wonderfully. The only problem I am encountering is for data set with duplicate values it ignores the duplicate. Say I have a data set called "Data" with the following: 2, 2, 2, 3, 3 and 8. If I do Max(Data, 4) if gives me 2, but if I do Max(Data, 5) it gives me nothing. What I want is for when I do Max(Data, 5) for it to give me 2, Max(Data,6) = 2 and so on. Please help.

[marina24]

Thank you 🙂