Finding Attrition Count Month On Month

[sutharsan]

I need to find attrition count month on month i was able to do it in Qlik measure for current month and last month i need it to for all the months like to be used in the trend chart  i used the below expression for current and last month

=Count(DISTINCT {<employee_id= P({<date= {">=$(=Date(MonthStart(AddMonths(Today(),-1)),'YYYY-MM-DD'))<=$(=Date(MonthEnd(AddMonths(Today(),-1)),'YYYY-MM-DD'))"}>} employee_id) -
P({<date= {">=$(=Date(MonthStart(Today()),'YYYY-MM-DD'))<=$(=Date(Today(),'YYYY-MM-DD'))"}>} employee_id)>} employee_id)

how do i do get for all the months as i have data for past 2 years 

[Chanty4u]

Try this in script 

MonthYear:

Date(MonthStart(date), 'YYYY-MM') as MonthYear

Then in UI chart expression 

Count(DISTINCT 

  {<

    employee_id = P({<MonthYear = {"=$(=AddMonths(MonthYear, -1))"}>} employee_id)

    -

    P({<MonthYear = {"=$(=MonthYear)"}>} employee_id)

  >} employee_id)

[sutharsan]

Hi Chanty4u,

Thanks for the help, but it still didn’t work as expected in the UI. Is it possible to calculate it directly in the load script using a flag to identify churned employees month over month?

[Nagaraju_KCS]

try with this

Count(DISTINCT
{<employee_id =P({<Date = {">=$(=Date(MonthStart(Month), 'YYYY-MM-DD'))<=$(=Date(MonthEnd(Month), 'YYYY-MM-DD'))"} >} employee_id)
-
P({<Date = {">=$(=Date(MonthStart(AddMonths(Month, 1)), 'YYYY-MM-DD'))<=$(=Date(MonthEnd(AddMonths(Month, 1)), 'YYYY-MM-DD'))"} >} employee_id)
>}employee_id
)

[sutharsan · Accepted solution]

MONTH ON MONTH ATTRITION COUNT

Master_Workforce:
LOAD DISTINCT
[employee_id],
Date(MonthStart([DATE]), 'YYYY-MM') as Month_Year,
'Active' as Record_Type,
1 as Headcount_Count, // Counts for active staff
0 as Attrition_Count // Active people are not attrition
Resident Fact;

Map_Existence:
MAPPING LOAD DISTINCT
[employee_id] & '|' & Month_Year as %Key,
1 as Exists
RESIDENT Master_Workforce;

Concatenate (Master_Workforce)
LOAD
[employee_id],
Date(AddMonths(Month_Year, 1), 'YYYY-MM') as Month_Year,
'Attrition' as Record_Type,
0 as Headcount_Count, // They are gone, so 0 headcount
1 as Attrition_Count // But they count as 1 Attrition event
RESIDENT Master_Workforce
WHERE Record_Type = 'Active' // Only look at active people
AND ApplyMap('Map_Existence', [employee_id] & '|' & Date(AddMonths(Month_Year, 1), 'YYYY-MM'), 0) = 0
AND Month_Year < Date(MonthStart(Today()), 'YYYY-MM');

Now use the Month_Year dimension and sum(Attrition_Count) in your dashboard which will return Month on Month attrition count and obviously you can also get attrition rate from this.