topic Re: How to get second to last value? in QlikView

How to get second to last value?

Mon, 13 Jul 2015 13:59:41 GMT

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.

Re: How to get second to last value?

Michiel_QV_Fan — Mon, 13 Jul 2015 14:01:05 GMT

Are you using these varables in expressions?

Re: How to get second to last value?

giakoum — Mon, 13 Jul 2015 14:01:50 GMT

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

Re: How to get second to last value?

sunny_talwar — Mon, 13 Jul 2015 14:02:18 GMT

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

Max(MyDate, 2)

Re: How to get second to last value?

Mon, 13 Jul 2015 14:09:58 GMT

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

Re: How to get second to last value?

Mon, 13 Jul 2015 14:12:47 GMT

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).

Re: How to get second to last value?

Mon, 13 Jul 2015 14:14:11 GMT

Wow, so simple. Thanks sunindia!

Re: How to get second to last value?

sunny_talwar — Mon, 13 Jul 2015 14:15:20 GMT

No problem

I am glad I was able to help.

Best,

Sunny

Re: How to get second to last value?

jorgett — Fri, 03 Jan 2020 19:55:29 GMT

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.

Re: How to get second to last value?

marina24 — Fri, 25 Feb 2022 15:11:12 GMT

Thank you 🙂