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: 
Not applicable

How to handle overlapping date ranges in Qlik?

I particularly want to know what would be the best approach in Qlik Sense to handle data that is based on overlapping date/time intervals. Let's consider an example.

In my data warehouse, which is a typical leave & attendance management system, I have dimensions — DimEmployee and DimDate and a fact called FactAttendance. This fact table has records that are stored as follows —
Problem Statement 2.png

This can be shown graphically as —

Problem Statement.png

In Qlik Sense, I need to create a report that allows end-user to filter data between two dates, i.e user will select a date range. Let's say, in this particular example, the user selects D1 through D20. Upon this selection, user should see the value — possibly from one of the measures — for how many days at least one of the employees was on leave. In this example, the user should see the sum of the light-blue segments in the bottom of the chart, i.e 11 days.

One of the approach will be to load the data in such a way that each leave has a linkage with employee as well as each of the dates, i.e the fact table should have one row per employee per date for each of the leaves taken. Using this approach also means that we increase the number of rows in our fact table exponentially. We may be adding redundancy into our warehouse if the fact has more columns that will have repeated values in each such row.

I am curious if there is a better way of doing this? An approach that does not include inserting numerous records into the fact table?

1 Reply
Gysbert_Wassenaar

Expand the intervals to days for each employee. Then count dates with at least on employee on leave. Something like count({<Date={'=count(EmployeeKey)>1'}>}Date)

See this blog post: Creating Reference Dates for Intervals


talk is cheap, supply exceeds demand