Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Multi-Field formula for Months

AlterixNoob
7 - Meteor

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 ExpenseExpense FromExpense TillTotal Days for expense
1,00015 March 201915 Nov 2019245

 

Output:

Total ExpExpense FromExpense TillTotal Days for expenseJanFebMarAprMayJunJulAugSepOctNovDecTotal
1,00015 March 201915 Nov 20192450065.30612122.449126.5306122.449126.5306126.5306122.449126.530661.2244901,000

 

Thanks

10 REPLIES 10
TheOC
15 - Aurora
15 - Aurora

hi @AlterixNoob 

How are your values for each month being worked out?


Bulien
AlterixNoob
7 - Meteor

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

Qiu
20 - Arcturus
20 - Arcturus

@AlterixNoob 

 

My  total days of expense is 246 days...1028-AlterixNoob.PNG

RobertOdera
13 - Pulsar

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

 

RobertOdera_0-1603853209373.png

 

 

The workflow is attached

RobertOdera
13 - Pulsar

Hi, @AlterixNoob 

 

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

Total Expense Days = 245.

Cheers!

 

RobertOdera_0-1603853554012.png

 

AlterixNoob
7 - Meteor

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
7 - Meteor

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
13 - Pulsar

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.

 

RobertOdera_0-1603890335983.png

The modified workflow is attached.

Cheers!

 

 

AlterixNoob
7 - Meteor

This is amazing, thanks RobertOdera!

 

Labels