Using a Beast Mode within a Beast Mode? (For Rolling Attrition)

nshively

Is this possible?

I'm trying to build a rolling attrition report and I have 2 separate attrition beast calculations (one for a single month's attrition and one for more than 1 month of attrition).

This works fine for showing point-in-time attrition (a year, a quarter, a month, etc.), but I've now been tasked with building a rolling attrition report that shows the last 12-months of attrition for each month of the chart. Picture a Jan-Dec bar chart with each month calculating the previous 12 months of attrition.

A colleague recommended a use the lag function to add the attrition formula by month and then order it by month.

However, I can't figure out how to put the attrition beast mode calculation into another beast mode calculation. I also tried using my attrition formula in the ETL and they didn't work at all.

Attrition formulas for reference: Terms/Average Headcount

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

This takes the terms for a single month and divides it by the ending headcount, minus the new hires, adding back terms and dividing by 2.

Quarterly/Yearly Attrition: SUM(`TermCount`)/ ((SUM(`ActiveCount`))/COUNT(DISTINCT `EOM_DateKey`))

This simply takes the total terms for a period and divides it by the average headcount for each distinct month in that period.

DashboardDude

Hi @nshively ,

So I think I've done what you're asking. It's a bit of a doozy. Anyway you can share a screenshot of an ETL or something to give more context? Bascially I think you'll have to do the date operations function to calculate last 12 months by month and then use Unpivot to flip everytihing....a doozy.

Let me know