For the past few weeks I've been grappling with how to adjust the CancelDt and calculate tenure. Since I'm using Tableau on top of Teradata, I've dug into both and decided on the below.
These calculations are included in a monthly churn workbook I produce for our product team. The workbook is updated at the beginning of each month and analyzes churn and media adoption for the previous month, it's preceding three month average, and the same periods in the previous year. The dates are configured to update dynamically, so the current_date assignment is valid for the entire month the workbook is valid. Once the workbook updates for the following month, you'll be ahead and the current_date assignment will remain in the month ahead of the study month.
Previous method for adjusting the CancelDt and calculating churn:
In Teradata create a view or use custom query for Tableau
select RptMonth , Id , SignupDt - (EXTRACT(DAY from SignupDt) -1) as SignupMo , CancelDt - (EXTRACT(DAY from CancelDt) -1) as CancelMo from foo
Then in Tableau, create a custom metrics to resolve the future date
IIF([CancelMo] > TODAY(), DATETRUNC('month',TODAY()),[CancelMo])
and to calculate "Tenure"
Push it all back to the database:
, CASE WHEN CancelDt = '2999-01-01' THEN current_date ELSE CancelDt END CancelDtUpd
, (CancelDtUpd - SignupDt) month(4) as Tenure
, CASE WHEN CancelDt = '2999-01-01' THEN 1 ELSE 0 END PayingFlag