Handling Start and End Dates

    i

     

    Recently I've had to develop a system to handle start and end dates within QlikView. The data held a start and end date on each line and the user wanted to be able to select a date range and see all the lines that were active for that period.

     

    This proved a challenge because of the overlap. For example some records started before the period selected but were active during it, some records started during the period and continued past the end and some began way before the period and finished afterwards. The diagram below indicates in green which records I wanted to include in the set.

     

    Start and End Dates.png

     

    First I tried using set analysis and managed to capture a number of the green examples above using various unions although it quickly became too complicated (I'd be interested to know if/how this could be done in Set Analysis simply).

     

    My final solution was to build a link table which contained every date for each record and flagged where the records were active and link the Fact table to the calendar table through this new table.

     

     

    Start and End Dates Structure.png

     

    I've attached the example qvw and look forward to your comments and improvement ideas

     

    Richard

    QlikCentral.Com