I can't quite figure out a formula to identify if a contract has been or was renewed. The challenge here is that the contract dimension changes when a renewal is done. Identifying a renewal as in example below is where the customer number and product code is the same and the next contract starts on or after the end date of the previous contract. It is common to have multiple renewals in historical data, meaning that given the criteria above contracts may have been renewed multiple times. The renewal flag is intended to identify if the unique Contract # has been renewed, meaning there was a contract for the same Cust # and Prod Code on/after expiration. Part of what is throwing me is if you have three or more renewals going into the past, the formula has to take into account the sequential order based on start and end date.
My preference would be to insert this renewal flag formula into the tableau prep flow vs performing in desktop. Thanks in advance
you can find out like this:
{FIXED Customer,Contract#:Max(End Date)}-{FIXED Customer,Contract#:MIN(End Date)}
if it returns value >0 then it should be renewal.
if thi sdoesn't work, can you post sme fake data to help you, but similar to your actual data .
