Workflow Help

[tpalensky002]

Hi All,

Thank you in advance for your help. I have a workflow I am trying to build and am not sure where to start. I have included a sample data set below. I want to sum Revenue for each customer but only for the first contract start date. For example, I want the total of Revenue for Customer 2200 for the contract that starts in 2003 but don't want to sum revenue for the 2007, 2011, or 2015 contract start dates. I have a large file with 7k+ unique customer IDs so it is obviously too much to do manually. Any help would be appreciated.

[MarqueeCrew · Accepted solution]

@tpalensky002,

I am a little confused by your question.  I think that the first step is to find the FIRST contract start date for each customer.  If a customer can only have 1 first date record, then you can use a SAMPLE tool (where incoming data is sorted by Customer ID + Contract Start Date) and select the 1st 1 record.  Then you'll see 2003 coming out for #2200 and 2004 for #1000.  If there are many first records possible, then you would SUMMARIZE.

To Summarize, you will first need to find the first year for each ID (either summarize with a groupby ID and MIN for Date or use the Sample technique above).  Then JOIN that result to the original data on ID + Date.  Now you will only get data for the first date coming through the join (eliminate all duplicate fields).  Next you use the SUMMARIZE to group by the ID and get the SUM of the revenue.

Hopefully, this solves your challenge.  If it doesn't, you might need a better example set of data and results.

Cheers,

Mark

[tpalensky002 · Accepted solution]

Hi Mark,

Thanks so much for your help! I was able to accomplish it using the SUMMARIZE and JOIN tool in the second method you described. 

You rock!