Company Turnover %

Stu5677
Stu5677 Member
in Datasets

I am trying to calculate the company turn over %. We are pulling data from Salesforce into Domo. We have a custom object that lists all employees. These employee have an active/inactive status and a termination date. I am trying to figure out a way to calculate the total ACTIVE by INACTIVE YTD based on that term date. Everything I have tried seems to either not work or not count properly. Is there a easy way to do this?

Tagged:

Answers

  • MichelleH
    MichelleH Coach

    @Stu5677 What have you tried so far?

  • Stu5677
    Stu5677 Member

    @MichelleH - I have tried to take the record Id counts of all active employees by the total separations (which worked in the opposite way, as the date filter was limiting the TOTAL active). I have also tried a few beast modes "CASE
    WHEN SUM(CASE WHEN User - Active = 'true' AND YEAR(Official_Termination_Date__c) = YEAR(CURRENT_DATE()) THEN 1 ELSE 0 END) = 0
    THEN 0
    ELSE
    SUM(CASE WHEN User - Active = 'false' AND YEAR(Official_Termination_Date__c) = YEAR(CURRENT_DATE()) THEN 1 ELSE 0 END) /
    SUM(CASE WHEN User - Active = 'true' AND YEAR(Official_Termination_Date__c) = YEAR(CURRENT_DATE()) THEN 1 ELSE 0 END) * 100
    END". Also tried to do this in MYSQL but that didnt work as expected.

    I basically need something to calculate the TOTAL active, by the separations.. which I even tried to load just one excel file that just listed the total active (200 EE) and tried to merge with my salesforce load, but that didnt seem to work as expected either..

  • DavidChurchman
    DavidChurchman Coach

    What is the termination date for active users? If it's null, then it looks like you would be dividing by 0, because no active users would have a term date the same as this year. Do you even need to check the termination date of active users, since I assume the definition of active users is that they are still there this year?

    Again, assuming that it's null, you could also wrap the termination dates in your BeastMode with an ifnull() that fills the null values with a date arbitrarily far in the future, and then instead of checking to see if the termination date is this year, you check to see if it is less than or equal to this year.

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

Categories