How to Create Monthly Averages for Headcount?

nshively

Hi All,

I'm in the process of building an attrition model in Domo in the form:

Total Terms / Average headcount

Terms is easy, that's just a count of everyone with a term status for that time period. Headcount is where I'm getting stuck. My headcount numbers are currently aggregated based on headcount at the end of each month. I've just been using this number so far, but I'm about 1/10% off in terms of accuracy.

In order to get average headcount, it should be:

((Current Month's headcount + Last Month's headcount) / 2)

I could write this manually for each month for the last decade, but that would take a lot of time and require I update the code every month, which I would prefer not to do. Is there a way to do this in Beast Mode with a couple of lines instead of a line for every month?

Thanks

MarkSnodgrass

What columns are currently in your dataset? I'm having trouble picturing what your data looks like. Do you have one row per month right now? If you can explain a bit more what your data looks like, it will be easier to suggest a solution.

GrantSmith

I'd recommend using a custom date dimension with offsets so you can easily have this month and last month together based on your current month. I've done a more detailed writeup on it here: A more flexible way to do Period over Period comparisons You may need to tweak it if your data is on a monthly cadence instead of daily.

Then you could use a beast mode like:

(SUM(CASE WHEN `Period Type` = 'Current' THEN `Headcount` ELSE 0 END) + SUM(CASE WHEN `Period Type` = 'Last Month' THEN `Headcount` ELSE 0 END)) / 2

nshively

@MarkSnodgrass @GrantSmith

My table has a row for every employee for every month with all of their statuses and a count of total employees and total terminations. An employee who has been here 12 months has 12 rows of data. Example below:

MarkSnodgrass

I would suggest creating an ETL that groups the data by month/year and sums the employee count and term count. You can then add a rank and window tile and use the lag function to get the total employee count for the previous month as a column right next to the current month. Next, add a formula tile to add your current and previous month and divide by two to get your average headcount.

This should get you all the datapoints you are looking for.

nshively

@MarkSnodgrass

I appreciate the suggestion, but I would prefer doing it at the BeastMode level if possible. There are about a dozen factors I need to be able to slice this data by and aggregating it at the ETL level will remove that functionality.

MarkSnodgrass

@nshively Understood. I'm sure @GrantSmith will come up with a very elaborate beast mode for you.

You can whatever you are potentially slicing by into your group by and rank and window tile and then save the final formula tile work that calculates the average headcount for your beast mode. I have done this multiple times and it allows me to maintain the card flexibility while avoiding complex beast modes that are hard to troubleshoot.

GrantSmith

@nshively

I'm not certain what all information you need displayed on your card but you could utilize the LAG window function assuming you don't have any missing months:

Average Head Count (Beast Mode)

( SUM(SUM(`EmployeeCount`)) OVER (PARTITION BY `EOMDate`) + LAG(SUM(`EmployeeCount`), 1) OVER (ORDER BY `EOMDate`)) / 2

nshively

Thanks @GrantSmith

I feel like that should work, but Domo is not recognizing 'OVER' or 'LAG()' as functions in Beast Mode?

GrantSmith

You'll need to talk with your CSM to get Window Functions turned on as it's a feature switch they need to do.

nshively

Hi @GrantSmith,

Thanks for the help so far, and my team is going to chat with the CSM about the window function. I did find a temporary solution for building out monthly attrition. It's not perfect, but it's the closest I think I can get without the Lag function:

SUM(`TermCount`)/ (((SUM(`ActiveCount`))+(Sum(`ActiveCount`) + (Sum(`TermCount`)-SUM(`NewHireCount`))))/2)

Basically, it's finding the average based on the combination of current headcount + last month's headcount (using current headcount +/- this month's terms and new hires).

However, due to how my tables are built, this only works for Monthly attrition. I'm trying to build yearly attrition, which should be easier because I can just divide by total months, however, I only have 10 months data in 2021. So I'm trying to build a rule to use different formulas based on the year, but it's not working at all. Here's where I'm at:

(CASE 

 When YEAR(2021) Then SUM(`TermCount`)/ ((SUM(`ActiveCount`))/10)

 When YEAR(2020) Then SUM(`TermCount`)/ ((SUM(`ActiveCount`))/12)

 Else 0

 End)

I tried building out the Case statement as a different beast mode calculation to = 10 or 12 (based on the year), but when I tried to use this as a variable in my statement it just copied the whole thing over instead of using it as a measure.

nshively

Nevermind, I realized it was a simple solution since I have the EOMDate:

SUM(`TermCount`)/ ((SUM(`ActiveCount`))/COUNT(DISTINCT `EOM_DateKey`))

It just divides the total count of active employees per month per year by the number of months of data for that year.

GrantSmith

I'm glad you got it figured out! I was going to suggest programmatically calculating the number of months to divide by depending on the current year and month (untested - back of napkin):

(CASE

 When YEAR(`EOMDate`) = YEAR(CURRENT_DATE) Then SUM(`TermCount`)/ ((SUM(`ActiveCount`))/MONTH(CURRENT_DATE))

 When YEAR(`EOMDate`) = YEAR(CURRENT_DATE) - 1 Then SUM(`TermCount`)/ ((SUM(`ActiveCount`))/12)

 Else 0

 End)