Retention Summary

[jamesmf1982]

Hi,

I have a chart that returns the length of an employees retention on an employee by employee basis using the following statement:

if(isnull(EmployeeLeaveDate), if($(v_date_max) - EmployeeStartDate<365,1,0))

This works fine, however I need to be able to summarise this to give the overall retention of the office. I have tried summing it and counting but neither work.

In addition on my table whilst I have the sum at the top of the table it does not return any values.

Any help would be great.

James

[sunny_talwar]

Doing this doesn't work?

Sum(If(IsNull(EmployeeLeaveDate), If($(v_date_max) - EmployeeStartDate < 365, 1, 0)))

[jamesmf1982]

Afraid not, it comes up with blank cells.

[sunny_talwar]

Would you be able to share an image with two expressions

1) If(IsNull(EmployeeLeaveDate), If($(v_date_max) - EmployeeStartDate < 365, 1, 0))

2) Sum(If(IsNull(EmployeeLeaveDate), If($(v_date_max) - EmployeeStartDate < 365, 1, 0)))

[jamesmf1982]

Hi there,

Please see below.

The first one is where I have the full details and the working formula. In the second I have hidden some of the fields to try and get the summary working.

[sunny_talwar]

Do you have an expression behind this?

$(v_date_max)

or is this just a static number?

[jamesmf1982]

Hi, Sorry it is max(CalendarDate) which represents the maximum date selected in the current selection.

[sunny_talwar]

Does this number needs to be constant across all dimension you have in your chart or is this max based on the dimension?

[jamesmf1982]

Hi, It is constant across all dimensions yes

[jamesmf1982]

Or... How could I change this to using Set Analysis? I have the following so far:

count({<EmployeeStatus -= {'Permanently Inactive'}, EmployeeStartDate = {"$(='>=' today()-365">} EmployeeCode)