Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

filter dataset on a reference date in qlik sense

This is a simplified version of my dataset: 

PersonNameAmountDate valid fromDate valid to
123Jack10001-01-201701-01-2019
123Jackson10001-01-201931-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?

Labels (1)
2 Replies
Highlighted
Partner
Partner

Re: filter dataset on a reference date in qlik sense

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

 

 

 

Work smarter, not harder
Highlighted
Contributor II
Contributor II

Re: filter dataset on a reference date in qlik sense

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?