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.5K Product Ideas
 1.5K Ideas Exchange
 1.4K Connect
 1.1K Connectors
 283 Workbench
 4 Cloud Amplifier
 4 Federated
 2.7K Transform
 90 SQL DataFlows
 569 Datasets
 2K Magic ETL
 3.4K Visualize
 2.4K Charting
 601 Beast Mode
 15 App Studio
 29 Variables
 600 Automate
 151 Apps
 418 APIs & Domo Developer
 29 Workflows
 2 DomoAI
 28 Predict
 12 Jupyter Workspaces
 16 R & Python Tiles
 362 Distribute
 99 Domo Everywhere
 261 Scheduled Reports
 2 Software Integrations
 98 Manage
 95 Governance & Security
 15 Product Releases
 Community Forums
 37 Getting Started
 28 Community Member Introductions
 90 Community Announcements
 4.8K Archive