Multi-Field formula for Months

[AlterixNoob]

Hi Guys - So I have a huge file which mentions the total expense for the year. I want to break down the expense into months (so ideally I'd want to have one column for January, one for February and so on with the expense for that month being mentioned in that column).

In my data set I've calculated the start date of the expense and the end date of the expense (it could be starting in the middle of the year and ending in the middle of the year as well). What's the best way to do create a monthly schedule? I assume there would be a multi field formula i can use which will automatically do this for me. See below an example from the data set

Data

Total Expense	Expense From	Expense Till	Total Days for expense
1,000	15 March 2019	15 Nov 2019	245

Output:

Total Exp	Expense From	Expense Till	Total Days for expense	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total
1,000	15 March 2019	15 Nov 2019	245	0	0	65.30612	122.449	126.5306	122.449	126.5306	126.5306	122.449	126.5306	61.22449	0	1,000

Thanks

[TheOC]

hi @AlterixNoob 

How are your values for each month being worked out?

[AlterixNoob]

They are pro-rated for the number of days in each month

[Qiu]

@AlterixNoob 

My  total days of expense is 246 days...

[RobertOdera]

Hi, @AlterixNoob 

Please like + mark as an acceptable solution if this works for you.

I've highlighted the areas that you want to look at closer in yellow

I've added -1, +1 treatments for Start Month count and End month count, respectively

The workflow is attached

[RobertOdera]

Hi, @AlterixNoob 

Forgot to add the transformation snapshot, before transposing to your use case.

Total Expense Days = 245.

Cheers!

[AlterixNoob]

Thanks! Appreciate the response however I have an extremely large data set (around 400,000 lines). If I generate rows for each of the days for each of the lines it will take ages to run.

[AlterixNoob]

Thanks, while this solution will work it has the same issue i mentioned above. I have around 400,000 lines in my data set. If i was to generate rows for each date within the line it will be very time consuming to run the workflow (I don't think it would run).

[RobertOdera · Accepted solution]

Sure thing, @AlterixNoob 

Your feedback + additional clarity on lines/ rows = some approach adjustments.

Please like + mark as an acceptable solution, if this works for you.

• Find & Replace Tool is sub-optimal past 100,000 rows. Therefore we will now use Join + Union.
• Your initial brief seemed to focus on "Days," but now we can invoke a "Month" proxy for the Generate Rows Tool and tweak it for the Effective Expense Days duration in Formula Tool.
• I have highlighted points of interest in yellow.

The modified workflow is attached.

Cheers!

[AlterixNoob]

This is amazing, thanks RobertOdera!