Using a Beast Mode within a Beast Mode? (For Rolling Attrition)

nshively

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 point-in-time attrition (a year, a quarter, a month, etc.), but I've now been tasked with building a rolling attrition report that shows the last 12-months of attrition for each month of the chart. Picture a Jan-Dec 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.

DashboardDude

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

nshively

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`)