Split Periods Into Rows Dynamically

Version 10

    The difficulty of displaying  FAQ: Open & Close Dates  over time is experienced by many.

    My hope is that the technique shared below will make it easier to do this for more people.





    Whether one should split the period into two (union), days, months, or even years, depends much on the length of the periods and number of records.


    Split intoScenarioFor
    YearsInsurance etc.Big data and long periods
    Two (Union)Big data and long periodsSee also
    MonthsInsurance etc.From a few months to a year
    if number of records isn't too extreme
    DaysHotels, etc.Short periods of a few days


    We can pre-calculate the cost of each method with a normal connection. The below is from the attached workbook:


    Dynamic Cross Join - Cost of Method.png


    The example below is based on the "Insurance" data.





    Include a reference table (Period) with running numbers from 0 to the maximum number of calendar periods needed or more. The attached has 120 rows in the Sheet Period.


    Dynamic Cross Join - Data (Insurance).png




    Cross join the data table (Insurance) with the "calendar period" table (Period) with a calculated field equal 1 on both sides:


    Dynamic Cross Join - Join (Insurance).png





    As the last step before building our view, add a filter that acts as a "JOIN" condition:


    Dynamic Cross Join - Filter (Insurance).png




    Now we are ready to build our views!


    Dynamic Cross Join - Build (Insurance).png




    Cover Days:

    DATEDIFF( 'day', [Cover Start Date], [Cover End Date] ) + 1

    Days In Period:


    MAX( [Cover Start Date], [Period Month Start] ),

    MIN( [Cover End Date], [Period Month End] )

    ) + 1

    Premium In Period:[Days In Period] / [Cover Days] * [Premium]





    Attached Workbook Version:  10.3