Post reply

Custom Aggregations (Measuring Headcount Attrition).

  • January 22, 2015 at 2:36 pm

    #151397

    Hello,

    I've got a measure, headcount attrition, that I'm not sure how to aggregate in a cube. Here's some example data, assume the time for all these is January 2015.

    Name|Department|MoveTo

    Jim|Sales|External

    Diane|Sales|Finance

    Mark|Finance|External

    Sarah|IT|Sales

    Assume an organizational hierarchy that has departments: Sales, Finance, & IT rolling up to "MyCompany".

    Now, my issue is to accurately measure attrition. Given the above data, when looking at the top of the house --"MyCompany" the attrition should be 2. Only 2 people have left the company, the rest are internal transfers. If I drill down to the departments I should have Sales with 2, Finance with 1 and IT with 1.

    I'm stuck on how to implement this. Any ideas? It's like I want to aggregate on the organization hierarchy in reverse, from top to bottom instead of aggregating up, but I just can't see how to do this.

  • January 22, 2015 at 8:01 pm

    #1772466

    Perhaps a simplified version of the method in the following article would do. It would also help a lot with other things you might ask of the hierarchical data.

    http://www.sqlservercentral.com/articles/T-SQL/94570/

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply