4 Replies Latest reply on Nov 14, 2017 5:53 AM by David Switzer

# Showing an "aging" in Tableau - (cohort analysis)

Using Tab10.

Trying to get a cumulative bad% of funded account vintage across time for comparison.

I can do this.

Calculate #months between origination and badMonth as M0 (bad in same month of origination), M1 (bad in first month after origination), etc.

Step1: create table with origination month on one axis -- and Mxxx on other axis -- count the bads

Step2: change calculation to running sum - so M1=M1+M0, M2=M2+M1+M0, M3=M3+M2+M1+M0, etc.

Step3: Use analysis to get % row

Output looks fine and accurate.

However...there is a time component to an aging.

If today is 12/1/16 - when you look at originations in May 2016 -- only M0-M8 are real

in Jun 2016 only M0-M7 are real, etc

So I need to either:

1. no value in the M's that have yet to happen

or

2. color out the M's that have yet to happen

It is not as simple as -- is the cell incrementally higher than previous cell - because it coule legitimately be zero.

It really has to be a function of dates. For example - see Sep '16 below. M0-M2 should be displayed because we are in December -- so Sep (M0), Oct(M1), and Nov (M2) are fully aged - and should show zero (which I can fix).

Make sense?

Ideal:

Message was edited by: David Switzer - uploaded packaged workbook

• ###### 1. Re: Showing an "aging" in Tableau - (cohort analysis)

Hey Dave,

When I open the .twbx it asks me to login into SQL server :-)

Can you run an extract on the data source and re-atach the .twbx :-)

Best,

Sujay

• ###### 2. Re: Showing an "aging" in Tableau - (cohort analysis)

Thanks - will upload the correct one tomorrow. Much appreciated.

• ###### 3. Re: Showing an "aging" in Tableau - (cohort analysis)

Were you able to find solution to this problem?

I am facing a similar problem as well.

Here is the Link:

• ###### 4. Re: Showing an "aging" in Tableau - (cohort analysis)

Yes -- I modified the calculation. Basically -- since this by origination date -- I add mNum (the numeric part of M0,M1,M2) as months to the origination date (first of month) -- and compare for whether or not to include the vale.

Note - there is no ELSE statement - if I recall correctly, omitting the ELSE statement was important.

sum(if  DATEADD('month', [mNum], DATETRUNC('month', [OriginationDate])) - 1 < DATE(TODAY()) then [cYieldAmt] end)/

sum(if DATEADD('month', [mNum], DATETRUNC('month', [OriginationDate])) - 1 < DATE(TODAY()) then  [Loan_Amount] end)