Date scaffolding with multiple different date measures?

Question

I want to show the amount of people in each contract status historically. I have a list of every contract's start date, suspension dates, expiration date, and termination state. As a brief example this is what my table looks like:

Client	Location	StartDate	ExpDate	SuspensionStart	SuspensionEnd	TerminatedDate
Jane	NJ	1/1/22	1/1/23	3/1/22	5/1/22	NULL
John	NY	11/15/22	11/15/23	NULL	NULL	3/8/22
Alice	NY	3/12/21	3/12/22	6/1/21	8/1/21	NULL
Jack	NJ	6/20/21	6/20/22	NULL	NULL	NULL

My goal is to get my table to look like this for the month of March

Active	Suspended	Expired	Terminated
1	1	1	1

Then be able to drill down by location too.

Since I have two variables that I want to count by the date (count if expdate=month/year and count of terminateddate=month/year) and then two variables with through dates.

One more piece of context...this data is pulled from a using a sql query from a shared snowflake database. There is no calendar table and I cannot create one except by a view which I used

select 
dateadd(day,seq,dt::date) dat 
,year(dat) as "YEAR"
,quarter(dat) as "QUARTER OF YEAR"
,month(dat) as "MONTH"
,day(dat) as "DAY"
,dayofmonth(dat) as "DAY OF MONTH",
       dayofweek(dat) as "DAY OF WEEK",dayname(dat) as dayName,
       dayofyear(dat) as "DAY OF YEAR"
from (
select seq4() as seq,  dateadd(month, 1, '2015-01-01'::date) dt
 from table(generator(rowcount => 16000))
)

I haven't used scaffolding before, and unsure which date to build the relationship on/join on?

Answer 1

Scaffolding is best done by Tableau Prep. There are multiple steps involved and Prep can step through them while it is very challenging with Tableau Desktop. See https://www.tableau.com/about/blog/2018/12/scaffold-data-tableau-prep-fill-gaps-your-data-set-99389 for one example of how to scaffold the data.

You can apply the techniques in the blog article and create the four metrics that you want to show.