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.7K Product Ideas
 1.7K Ideas Exchange
 1.5K Connect
 1.2K Connectors
 292 Workbench
 4 Cloud Amplifier
 8 Federated
 2.8K Transform
 95 SQL DataFlows
 600 Datasets
 2.1K Magic ETL
 3.7K Visualize
 2.4K Charting
 685 Beast Mode
 43 App Studio
 38 Variables
 655 Automate
 170 Apps
 438 APIs & Domo Developer
 42 Workflows
 5 DomoAI
 32 Predict
 12 Jupyter Workspaces
 20 R & Python Tiles
 383 Distribute
 110 Domo Everywhere
 267 Scheduled Reports
 6 Software Integrations
 111 Manage
 108 Governance & Security
 8 Domo University
 25 Product Releases
 Community Forums
 39 Getting Started
 29 Community Member Introductions
 98 Community Announcements
 Domo Community Gallery
 4.8K Archive