Using a Beast Mode within a Beast Mode? (For Rolling Attrition)
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 pointintime attrition (a year, a quarter, a month, etc.), but I've now been tasked with building a rolling attrition report that shows the last 12months of attrition for each month of the chart. Picture a JanDec 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.
Answers

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
0 
So what my colleague recommended was something like this:
Attrition +
Lag(sum(attrition),1) over (order by date) +
Lag(sum(attrition,2) over (order by date) +
etc. for 12 months.
I tried this using the actual formula and the validator accepted it, but it broke when trying to put it into a chart.
This is where I left off:
SUM(`TermCount`)/ (((SUM(`ActiveCount`))+(Sum(`ActiveCount`) + (Sum(`TermCount`)SUM(`NewHireCount`))))/2)
+
lag(SUM(SUM(`TermCount`)/ (((SUM(`ActiveCount`))+(Sum(`ActiveCount`) + (Sum(`TermCount`)SUM(`NewHireCount`))))/2)),1) over (order by `EOM_DateKey`)
+
lag(SUM(SUM(`TermCount`)/ (((SUM(`ActiveCount`))+(Sum(`ActiveCount`) + (Sum(`TermCount`)SUM(`NewHireCount`))))/2)),2) over (order by `EOM_DateKey`)
0
Categories
 7.3K All Categories
 13 Getting Started in the Community
 141 Beastmode & Analytics
 1.8K Data Platform & Data Science
 54 Domo Everywhere
 2K Charting
 1K Ideas Exchange
 903 Connectors
 236 Workbench
 342 APIs
 77 Apps
 19 Governance & Productivity
 234 Use Cases & Best Practices
 50 News
 473 Onboarding
 572 日本支部