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 —
This can be shown graphically as —
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?