Alteryx designer Discussions

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

Split over contract period

Highlighted
6 - Meteoroid

Hi, 

 

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

 

WF.png

 

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

 

Revenue.png

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. 

 

Dan

Highlighted
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?

Labels