Split Periods Into Rows Dynamically

Version 10
Created by kettan on Jul 13, 2017 7:28 AM. Last modified by kettan on Jul 13, 2017 3:17 PM.

    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.

     

     

    METHOD

     

    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.

     

     

    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

     

    JOIN

     

    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

     

     

    FILTER

     

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

     

    Dynamic Cross Join - Filter (Insurance).png

     

    BUILD

     

    Now we are ready to build our views!

     

    Dynamic Cross Join - Build (Insurance).png

     

    Calculations:

     

    Cover Days:

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

    Days In Period:

    DATEDIFF('day',

    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