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 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.

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

  • nshively
    nshively ⚪️
    edited November 2022

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