Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Join us on Dec 2 for a half-day virtual analytics + data science event!
US & CA customers only

It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!

Split over contract period

6 - Meteoroid



I have a multi-year contract period and would like to split the revenue over the contract period. 


e.g. if contract is from Jul 1 2016 to Jun 30 2020 and contract amount is 700,000 in first year and 710,000 in the 2nd year,


the revenue in 2016 will be 700,000 / 2 = 350,000 and the revenue in 2017 will be 700,000 / 2  + 710,000/2 = 705,000.


How do I create the workflow for this?


I have attached the excel with data sample. Thank you!



17 - Castor
17 - Castor

Hi @ailee15 


Here you go




After removing the results columns from your input, the WF transposes Years and Months to get the data into a long format.  These are joined and unioned to give one row for each Contract and year.  The first Multi-row tool calculates the number of months to be allocated from the previous year.  The next one calculates the revenue for the current year taking into account any left over from the previous one.  A crosstab and rename to get the results in the correct format



Finally, these are joined back to your original data.


In the calculation for the revenue, I prorated the amounts based on the number of months in the start year. 



6 - Meteoroid

Hi Dan, 


Thanks a lot! 


If I want to add the formula to calculate the # of months in each year, how can I do that? 


Can you please help me?