I want to get the period attrition rate which is using the total left employees during that time divided by the total employee at the beginning of the period. Below is the formula I am using: sum(distinct_count({left_id},[monthstartdate])) / (coalesce(distinct_count(ifelse(monthstartdate={Min Start Date},emplid,NULL)),0)-

coalesce(distinct_count(ifelse(monthstartdate={Min Start Date},{new_id},NULL)),0)).

Explanation for numerator: sum(distinct_count({left_id},[monthstartdate])) — This is to get sum of distinct left employees per month. Because the data set is per seller per compplan. A seller may change the comp plan in one month, so I used distinct count to remove monthly dup. At the meantime, a seller may leave and rejoin the team several times during the period and all the movement has to be captured, so I can’t use count at the overall level.

Explanation for denominator: (coalesce(distinct_count(ifelse(monthstartdate={Min Start Date},emplid,NULL)),0)-

coalesce(distinct_count(ifelse(monthstartdate={Min Start Date},{new_id},NULL)),0)) This is to get the number of employee at the beginning of the period.

I got an error message: LAC AGG is not valid. Could someone please help?

You can’t sum an aggregation.

sum(distinct_count

you could look to sum (distinctCountOver

for more information here is all the logic you’ll need.

Hi @Cindy ,

Checking in. We have not heard back from you regarding your question. We’d still like to help. If we do not hear back in the next 7 days, we will archive the question.

Regards,

Koushik