Cohort Analysis

[Anonymous]

Hi All,

I need help from you guys,this is regarding Cohort Analysis. I have attached an image below, column ''Joined'' gives me the count of people joined in that particular month.Next Column, ''Month1 Left" gives me, After 1 month how many are left in the organization who have joined in that particular month,

Similarly Column ''Month2'' gives me After 2 months how many are left in the organization who have joined in that particular month and so on up to 12 months.

Ex.In Apr-16, 900 people had joined,after completion of month Apr-16, 875 are left in organization(means 25 had Attrited),after completion

of month May-16, 850 are left in organization....so on...after completion of mar-16, 785 people are left

Similarly I need it for all the months.

To get the Joined count, I have used the below formula and i'm getting the count correctly,

=Sum({<FiscalYear={"$(=Max(FiscalYear))"}>}NEW_JOINEE_COUNT)

Note:NEW_JOINEE_COUNT is the field created at backend, its actually a calculated flag.

For Month1 Left,i'm getting the correct values,formula I have used is,

=If(Only({$<FiscalYear=,FiscalMonth=,FiscalMID={">=$(=min({<FiscalYear={$(=Max(FiscalYear))}>}FiscalMID)) <=$(=min({<FiscalYear={$(=Max(FiscalYear))}>}FiscalMID)+11)"}>}FiscalMID),

[New Hire]-Count({$<FiscalYear=,FiscalMonth=,FiscalMID={">=$(=Min(FiscalMID))"},NEW_JOINEE_COUNT={1},Status_Inactive={1}>}DISTINCT EMPLOYEE_CODE))

Note:FiscalMID is Month ID,[New Hire] is the expression name I have used for joined count

I'm Having problem in getting the values for further months(as shown in the pic) so please help me doing this

[MK_QSL]

Cohort Analysis

[Anonymous]

Hi Manish,

Thanks for the reply, I have gone through that link before,But I wanted it in straight table with the requirement I have posted.

And I am able to achieve it for the first month using the expression posted, not for further months.Each month will be having different expressions.I'm asking how to get it for further months.

Regards,

Sumanth