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 into | Scenario | For | |||
| Years | Insurance etc. | Big data and long periods | |||
| Two (Union) | Big data and long periods | See also | |||
| Months | Insurance etc. | From a few months to a year if number of records isn't too extreme | |||
| Days | Hotels, 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:
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.
JOIN
Cross join the data table (Insurance) with the "calendar period" table (Period) with a calculated field equal 1 on both sides:
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.
BUILD
Now we are ready to build our views!
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
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
Comments