topic Re: Retention Summary in QlikView

Retention Summary

jamesmf1982 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Retention Summary

sunny_talwar — Mon, 12 Feb 2018 12:29:44 GMT

Doing this doesn't work?

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

Re: Retention Summary

jamesmf1982 — Mon, 12 Feb 2018 12:38:57 GMT

Afraid not, it comes up with blank cells.

Re: Retention Summary

sunny_talwar — Mon, 12 Feb 2018 12:40:51 GMT

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)))

Re: Retention Summary

jamesmf1982 — Mon, 12 Feb 2018 13:16:24 GMT

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.

Re: Retention Summary

sunny_talwar — Mon, 12 Feb 2018 13:18:06 GMT

Do you have an expression behind this?

$(v_date_max)

or is this just a static number?

Re: Retention Summary

jamesmf1982 — Mon, 12 Feb 2018 13:26:20 GMT

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

Re: Retention Summary

sunny_talwar — Mon, 12 Feb 2018 13:28:01 GMT

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

Re: Retention Summary

jamesmf1982 — Mon, 12 Feb 2018 15:24:33 GMT

Hi, It is constant across all dimensions yes

Re: Retention Summary

jamesmf1982 — Mon, 12 Feb 2018 16:05:10 GMT

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

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

Re: Retention Summary

sunny_talwar — Mon, 12 Feb 2018 16:30:19 GMT

Can you try this

Sum(If(IsNull(EmployeeLeaveDate), If(Max(TOTAL CalendarDate) - EmployeeStartDate < 365, 1, 0)))

Re: Retention Summary

sunny_talwar — Mon, 12 Feb 2018 16:31:19 GMT

Only thing I see off is the set analysis on StartDate... try this

Count({<EmployeeStatus -= {'Permanently Inactive'}, EmployeeStartDate = {"$(='>=' & Date(Today()-365))"}>} EmployeeCode)

Re: Retention Summary

jamesmf1982 — Tue, 13 Feb 2018 10:59:59 GMT

Awesome! Thanks for that