Help with Forecasting

Hi Community,

I'm trying to produce some aggregated forecast and was in need of some help, mainly around how to summarize the data before passing it through the TS models. The data is at the employee level but we'd like to have it summarized to the dept ID level. Below is my problem statement, and then a desired outcome. The dataset is also attached.

Problem Statement: We'd like to forecast out attrition in our org for the next three months by dept ID.

Desired Outcome: two columns, first column would have dept ID and the second column would have the forecasted attrition.

I know I need to summarize, but I imagine I need to summarize by date, and then add by Dept ID? Any help would be great.

Thank you.

sample.hr.data.xlsx

sample.hr.forecast.yxmd

Date Time

Transformation

Predictive Analysis

Accepted answers

All comments

echuong1

I would probably start by creating more generic date buckets like weeks or months (using the Formula tool). From there, you can create your model with the ARIMA and ETS tools. For multiple groups, I'd take a look at this thread:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/TS-Forecasting/td-p/28857

RolandSchubert

Hi @dlopez ,

my approach would be to convert termination dates to a weekly or monthly level (if you want to predict "next three month", I'd use monthly data) and summarize - GROUP BY dept ID and period (week or month), UID count. You could use ARIMA or ETS to predict attrition based on the aggregated values, TS Model Factory may be used to build the model for multiple depts.

Have you considered using attrition rate (calculate attrition rate per month and dept first, predict future attrition rates and calculate absolute values for attrition as a last step)? It could make sense if the number of employees in a department is strongly varying).

What do you think?

Best,

Roland

RobertOdera

Hi, @dlopez

Great question, and thank you for the problem statement and desired outcome.

Please like or mark an unacceptable solution if this works for you.

Here are some thoughts to explore:

1. Periodicity - break the Dates into the Weeks your HR recognizes (i.e., 52 weeks or 53 weeks), and you probably need only the last three years worth of data (for attrition recency + aged data effects might be more pronounced)

2. Descriptive Stats - slice and dice to understand Dept ID participation in the data, and generate an allocative factor (that should be tweaked further by internal conversations that are on-going + target strategies that have been deployed). At the end of this exercise, you want to be able to complete the following statement, "Dept X represents Y% of Total Attrition for period Z."

3. To start, run your TS as an aggregate = do not forecast per Dept ID. Just forecast based on Period and Attrition Count.

4. Once you have your TS forecast output range (there will be a number + low + high = the cone of uncertainty), ALLOCATE the forecast to Dept ID based on step 2.

5. Tweak (a little plus or minus) based on current ongoing initiatives or absence of initiatives

6. Optional: In step 2, you might also assess the time to attrition events/ clusters and also generate this statement, "Major/ Minor/ Average Attrition tends to happen X time (weeks) after Hire Date for Dept X."

7. Optional. Use step 6 to ALLOCATE which Dept ID are at risk for attrition events

Of course, by the time you're considering 6 and 7, you will be using more than TS. You will be augmenting TS with Predictive and Prescriptive treatments. Cheers!

dlopez

Hi @echuong1

Got it, and that was my initial steps, bucket day level dates into months since that's what I want to forecast. And then I ran them through ETS and ARIMA models, working on that second part you mentioned.

dlopez

Hi @RolandSchubert,

That's what my initial steps were (workflow has been shared), to group day level dates into months and then run them through the ETS and ARIMA models. Now I'm installing the TS Model tools to do the grouping of depts.

I mentioned this to our business partner and we've decided absolute numbers would work best for what they're using them for.

Thanks!

david

dlopez

Hi @RobertOdera

Thanks for your feedback, was very helpful. My comments to your questions are below.

1. Exactly, this is was I did. Took the dates and aggregated them to the monthly level.

2. We've already done this piece and are aware, and the business exercise we really want to answer is very similar. "in the next three months these depts are forecasted to lose xxx amount of employees" that way we could allocated resources where we see higher levels of forecasted attrition

3. Did this, workflow has been attached.

4 thru 7 Not sure how I'd allocate if I didn't include dept in the forecast, maybe I'm missing something here.

Again, thanks for the feedback was very constructive.

Cheers,

david

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703