Split Periods Into Rows Dynamically

Version 26

    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.

     

    OBS.

    Tableau has shared a recipe where you don't split rows.

    If it works for you, this recipe might be all you need:

     

    Showing Records That Fall Within a Period of Time | Tableau Software

     

     

    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:

     

    Filter:

    [Period] <= DATEDIFF( 'month', [Cover Start Date], [Cover End Date] )

     

    OBS.

    To generate fewer rows, this calculated filter can include start and end date parameters for displayed periods.

    See more in appendix at the end of this document.

     

    Dynamic Cross Join - Filter (Insurance).png

     

    BUILD

     

    Now we are ready to build our views!

     

    Dynamic Cross Join - Build (Insurance).png

     

    Calculation Overview:

     

    Filter:[Period] <= DATEDIFF( 'month', [Cover Start Date], [Cover End Date] )
    Period Month Start:DATE(DATETRUNC( 'month', DATEADD( 'month', [Period], [Cover Start Date] ) ))
    Period Month End:DATE(DATEADD( 'day', -1, DATEADD( 'month', 1, [Period Month Start] ) ))
    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]

     

     

     

    APPENDIX A - FEWER ROWS

     

    As already mention, we can split the period into fewer rows by including start and end period parameters in the formula.

    Besides adding the parameters [Period Start] and [Period End], the only other change was to modify these two formulas:

     

    Filter:

    [Period] <=

    DATEDIFF( 'month', MAX([Cover Start Date],[Period Start]), MIN([Cover End Date],[Period End]) )

    Period Month Start:

    DATE(DATETRUNC( 'month', DATEADD( 'month', [Period], MAX([Cover Start Date],[Period Start]) )))

     

     

     

    See more in the attached workbook named  [Dynamic Cross Join with reporting start and end parameters.twbx].

     

     

    APPENDIX B - NULL END DATE

     

    Although not included in attached workbooks, the handling of nulls is as simple as replacing it with another date, such as TODAY() or as below, with the parameter [Period End]:

     

    Filter:[Period] <= DATEDIFF( 'month', MAX([Cover Start Date],[Period Start]), MIN(IFNULL([Cover End Date],[Period End]),[Period End]) )
    Cover Days:

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

    Days In Period:

    DATEDIFF('day',

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

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

    ) + 1

     

     

    MORE EXAMPLES

     

    Re: how many employees are working by the hour of the day   is an example with hourly periods.

    The only difference is that various date functions use hour instead of { year, quarter, month, day } as argument.

     

     

    Attached Workbook Version:  10.3