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
 10.6K All Categories
 8 Connect
 918 Connectors
 250 Workbench
 474 Transform
 1.7K Magic ETL
 69 SQL DataFlows
 478 Datasets
 214 Visualize
 259 Beast Mode
 2.1K Charting
 12 Variables
 19 Automate
 356 APIs & Domo Developer
 89 Apps
 3 Workflows
 20 Predict
 5 Jupyter Workspaces
 15 R & Python Tiles
 249 Distribute
 65 Domo Everywhere
 243 Scheduled Reports
 21 Manage
 42 Governance & Security
 191 Product Ideas
 1.2K Ideas Exchange
 11 Community Forums
 27 Getting Started
 14 Community Member Introductions
 55 Community News
 4.5K Archive