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
John Le
You're only one dashboard away.
Click here for more video solutions:
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
 All Categories
 1.2K Product Ideas
 1.2K Ideas Exchange
 1.4K Connect
 1.1K Connectors
 273 Workbench
 2 Cloud Amplifier
 3 Federated
 2.7K Transform
 78 SQL DataFlows
 525 Datasets
 2.1K Magic ETL
 2.9K Visualize
 2.2K Charting
 437 Beast Mode
 23 Variables
 513 Automate
 115 Apps
 390 APIs & Domo Developer
 8 Workflows
 26 Predict
 10 Jupyter Workspaces
 16 R & Python Tiles
 332 Distribute
 77 Domo Everywhere
 255 Scheduled Reports
 66 Manage
 66 Governance & Security
 1 Product Release Questions
 Community Forums
 40 Getting Started
 26 Community Member Introductions
 68 Community Announcements
 4.8K Archive