How to Create a monthly forecast based on end and start dates

Question

Hii everyone, I'm hoping someone can help me. I've tried to use co-pilot to work through this, but it seems overcomplicated (but maybe that's just what it is?)

I have this data I receive on a daily basis - it's a list of contingent workers and it has a few components to it. Start and End Dates, dept, bill rate, Status of employment. While I already have a monthly forecast set up in excel, i wanted to automate the process of cleaning up the data and creating the forecast in Alteryx.

I've already gotten as far as cleaning up the data, but I would like to automate the monthly forecast piece.

Columns A-J is a copy and paste of the daily report I get (this is the part I already cleaned up using Alteryx)

Columns K-V is what I'm having trouble replicating in Alteryx. Essentially I want to add dates to the column header and use an "if formula" to calculate if the start and end dates are within that month, calculate the forecasted expenses for that month, using the bill rate and business days in the month. I like this format but i cannot seem to figure out how to create the monthly expense forecast view in Alteryx.

Book1.xlsx

dreldrel · Answer

The formula should be able to do it, the other idea is just to populate your cleaned data to Columns A-J and keep Columns K-V with original Excel formula. So you dont need to replicate those forecasting in Alteryx

lwolfie · Answer

I'm not sure if you've figured this out.  This could certainly be cleaned up, but this is how I would solve your question.

TransposeGrouping.yxmd

OTrieger · Answer

@sineads 
This is the excel formula:

=IF(OR($C4<P$3, $B4>EOMONTH(P$3,0)), 0,NETWORKDAYS(MAX($B4, P$3), MIN($C4, EOMONTH(P$3,0)))*8*$H4)

If you fully understanding this formula then you will be able to replicate it in Alteryx. Your key element is to understand the above and then you will manage to figure it out in Alteryx.

You can use the formula tool, there are plenty of options for date time calculations.