Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

Last but One Date/Price based on Condition

Hello Guys,

I am trying to find out the most recent price changes on Materials. Here is what I have in the table

Material      Valid From Date   Valid To Date    Price

123               01/01/2012          01/06/2012        10

123               01/07/2012          05/12/2013         20

123               06/12/2013          12/31/9999         30

As shown above, the Valid to date of the Last change is always set to 12/31/9999 which helps me get the price changed to (30 In this case). I am not sure how to the the price before last change (20 in this case).

Appreciate your help

Thanks

-Hari

Tags (1)
1 Solution

Accepted Solutions

Re: Last but One Date/Price based on Condition

This should do the trick:

FirstSortedValue(Price, -[Valid To Date],  2)

FirstSortedValue sorts the Price field by [Valid To Date] (descending because of the - sign), and then takes the second value because of the 2 in the third parameter.

6 Replies

Re: Last but One Date/Price based on Condition

This should do the trick:

FirstSortedValue(Price, -[Valid To Date],  2)

FirstSortedValue sorts the Price field by [Valid To Date] (descending because of the - sign), and then takes the second value because of the 2 in the third parameter.

Re: Last but One Date/Price based on Condition

Or may be this:

FirstSortedValue({<[Valid To Date] -= {'12/31/9999'}>}Price, -[Valid To Date])

Not applicable

Re: Last but One Date/Price based on Condition

Hi Nicole,

The solution worked like a charm. Appreciate you taking time to explain the function features below.

Good Day

Hari

oknotsen
Honored Contributor III

Re: Last but One Date/Price based on Condition

If your question is now answered, please flag the Correct Answer (via the big "Correct Answer" button near every post).

If not, please make clear what part of this topic you still need help with .

May you live in interesting times!
Not applicable

Re: Last but One Date/Price based on Condition

Hi Sunny,

Thanks for your quick response.

I am a little confused at the {<[Valid To Date] -= {'12/31/9999'}>}

part of the expression. Does "-=" take the previous value to '12/31/9999' ? or was that just a typo.

Any case I am wondering if the expression just gives out the Price when the Valid to Date is 12/31/9999 as 2 (Rank is not mentioned).

Appreciate your response.

Good Day

Hari

Not applicable

Re: Last but One Date/Price based on Condition

Done. Thanks for letting me know of this feature. I am new to these forums.

Community Browser