filter dataset on a reference date in qlik sense

[Lars_Esser]

This is a simplified version of my dataset: 

Person	Name	Amount	Date valid from	Date valid to
123	Jack	100	01-01-2017	01-01-2019
123	Jackson	100	01-01-2019	31-12-9999

When something changes, the old line is closed (the date valid to is changed  in the current date ), and a new line is added (with as date valid from the current date and data valid to 31-12-9999. 

We want to load both lines into qlik sense and show te actual line by default (record with date valid to = 31-12-9999). 

We want to have an option to change a field reference date to show the data that was valid on the selected date.

When we set the reference date on 01-05-2019 we still want to see the second line of our example. (01-05-2019 is between 01-01-2019 and 31-12-9999)

When we change it into 01-05-2018 we want to see the first record of our example.  (01-05-2018 is between 01-01-2017 and 01-01-2019

We never want to show both lines , because then calculation with ammount will not be correct (show 200 instead of 100). 

We tried using a datepicker and an input field to select a reference date, but we keep struggeling how to filter the data based on the reference date. 

Any tips or solutions?

[JordyWegman]

Hi Lars,

Did you try to create all the in between dates for when the date was valid? This way you can use this field in the date picker.

Check this solution and you can do this.

Btw, you valid from/ valid to is not correct. Because you say two lines should never be visible at the same time, but when I select 1-1-2019, both will show. 

The valid from on the 2nd record will always be the Date valid to + 1. 

Jordy

Climber

[Lars_Esser]

Thanks for the reply. We have a dataset with over 1,8 million records (including historical data for almost 4 years. 

When we want to create a record for each day this will create 365*4  years= 1460*1,8 million records = 2.628.000.000 with ur solution. And this number will only grow in the future. 

Is there no other solution?