Row Spilt as per Amount and Month

[raunakrenge]

 

Hi All, 

I'm looking  to build the solution for attached screenshot. If my start date is in June 2019 and it runs for the duration of 10 months then I should have 10 rows for that project starting with June 2019 til March 2020 (which is 10 month duration) and Yearly cost should be dividing equally among those duration. 

In summary -  I want to split one row into multiple (based on duration column data).

Thanks in advance.

R

[danilang · Accepted solution]

Hi @raunakrenge 

You can try something like this

Start by converting the Start to an Alteryx compatible format and generating the end month for each record.  Then generate the extra rows using Generate Rows tool.  Calculate the monthly cost and the month description, resulting in

 

Dan

[JoshuaGostick · Accepted solution]

Hi @raunakrenge,

I've built a workflow that should solve your issue. Please let me know if this works for you or if you have any questions.

Thanks,

Josh

[OllieClarke · Accepted solution]

Hi @raunakrenge you can use the generate rows to do this. Convert the start date to a date field, and then use this and the duration field to split your data

[JoshuaGostick]

Wow that's some weird coincidence to have three solutions within a minute hahaha!

[raunakrenge]

Thank you all for the amazingly fast and amazingly accurate answer. I tried all 3 and all are working fine for me. I appreciate your help 🙂

[ansars]

Hi ,

I have similar requirement where i need to split the Gross Revenue into Months. However i don't have the duration column handy. I need to first create the duration Column based on the difference from End Date & Start Date. Any suggestions how to bring in the duration first in the same workflow you provided.

[danilang]

Hi @ansars 

If you have [Start Date] and [End Date] you can use the following in a formula tool at the start of the workflow to get the duration

DateTimeDiff([EndDate],[StartDate],"months")

Dan