How to calculate YTD Attrition in qlikview?

[#1]

Hi,

I am trying to calculate YTD attrition %. I need a month on month view of attrition %. The formula I am using is Total Separation in a financial year / Total Headcount in a financial year *12. I am not able to calculate a accumulated count. I tried using the Full Accumulation, but still I am not getting the correct result.

[benvatvandata]

Could you provide a little more detail?

[Anil_Babu_Samineni]

Check whether it is suits for you? I assume you are using this? Can you post your Expression to calculate{I mean expression to work}

MCDFN -- Master Calendar Date Field Name

SET CountNumber = 12;

Sum({<[Financial Year]=, [Financial Month]=, [Financial Quarter]=, [Financial Week]=, DateField=, MCDFN={">=$(=Num(YearStart(Max(MCDFN))))<=$(=Max(MCDFN))"}>} TOTAL Separation)/

Sum({<[Financial Year]=, [Financial Month]=, [Financial Quarter]=, [Financial Week]=, DateField=, MCDFN={">=$(=Num(YearStart(Max(MCDFN))))<=$(=Max(MCDFN))"}>} TOTAL Headcount * $(CountNumber))

Note: This will give full accumulation for you

[#4]

Hi Ben & Anil,

Thanks for your quick response.

The issue here is I have the month on month employee headcount and attrition data in a same table which is classified as HC and Attrition. The below is the table structure:

HC Month               Emp ID               Status

7/1/2016                    1                         HC

7/1/2016                    2                         HC

8/1/2016                    1                         HC

8/1/2016                    2                         Attrition

Formula is  "Total Count of Attrition / Total Count of HC *12"

Count(if(Status='Attrition',[Emp ID]))/Count(if(Status='HC',[Emp ID]))*12

Pls help to solve this.

Thanks in advance

-Sakthivel

[vinieme12]

please post a sample

[Anil_Babu_Samineni]

When you are talking about Month on Month, I assume you may need this? From above data what was your expected output

SourceData:

LOAD

    [HC Month],

    Month([HC Month]) as Month,

    Date(MonthStart([HC Month]), 'MMM-YYYY') as [Month Year],

    Year([HC Month]) as Year,

    [Emp ID],

    Status;

Load * Inline [

HC Month,               Emp ID ,              Status

7/1/2016 ,                   1   ,                      HC

7/1/2016  ,                  2  ,                       HC

8/1/2016   ,                 1 ,                        HC

8/1/2016    ,                2,                         Attrition

];

For simple month to date comparison you need only two variables set:

set vMaxMonthYear = =Date(max([Month Year]), 'MMM-YYYY');

set vPriorMonthYear = =Date(addmonths(max([Month Year]), -1), 'MMM-YYYY');

And then the expressions for the current month and prior month columns are sorted to:

Count({<[Month Year]={'$(vMaxMonthYear)'},Year=,Month=, [HC Month]=, Status = {'HC'}>} TOTAL [Emp ID])

/

Count({<[Month Year]={'$(vPriorMonthYear)'},Year=,Month=, [HC Month]=, Status = {'Attrition'}>} TOTAL [Emp ID]) * 12

OR

Simple use this from your expression. From above data KPI Give 4.8 (If not, You may show expected output too)

Count({<Status='Attrition'>} TOTAL [Emp ID]))/Count({<Status='HC'>} TOTAL [Emp ID])*12

From Above Data,

2/5*12 = 4.8 // Output seems this

[benvatvandata]

Similar to the expression Anil posted, have you tried:

( Count( {< Status = {'Attrition'} >} Total [Emp ID] )   /   Count({< Status = {'HC'} >} Total [Emp ID] )  )*12

?

[benvatvandata]

Could you post an example of what the desired output is?