Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
siddharth_kulka
Creator II
Creator II

Display second record (max-1) based on selected end date

Hello,

Sample app and dummy data attached. App Data model is very much on similar lines (UI_EndDate is an island and not Joined/Linked to the fact table).

Scenario: Based on selected end date (Always one selected), display 'Limit' value for 2nd Max Analysis Date (Max [Analysis Date] -1) for rolling 12 month period in fact data.

3 examples to clarify:


1. If selected UI_EndDate is 11/4/2018, straight table should display below:

2. If selected UI_EndDate is 10/10/2018, straight table should display below:

3. If selected UI_EndDate is 10/1/2018, straight table should display below:

Any help, much appreciated.

Cheers!

2 Replies
tresesco
MVP
MVP

Try this expression :

Sum({<[Analysis Date]={"=Rank(If([Analysis Date]<=UI_EndDate,[Analysis Date]))=2"}>} Limit)

siddharth_kulka
Creator II
Creator II
Author

Hi Tresesco,

Firstly, thanks. Your feedback is accurate based on the question. However, realized I didnot communicate the requirement in its entirety.


Scenario (corrected): Based on selected end date (Always one selected), display 'Limit' value for 2nd Max Analysis Date (Max [Analysis Date] -1) for every record in rolling 12 month period. Re-attaching qvw and sample date.


Expected Output (Column 'Previous Value' needs to be calculated):

Expected Output.JPG

Thanks in advance!