Expression / Set Analysis assistance

Oggy172 · ‎2025-02-12

Hi all,

Had an issue raised with our retention app and at a loss as to how to formulate the fix.

The intended use to is to measure staff retention, however, the existing version of the app when filtered, displays inaccurate information. I'm not really sure what the intention was, it doesn't make much sense, anyway, I digress.

We have, from our Time Management software, a list of employees and their status' -

If Status = *Employee* they are 'Employed', otherwise they are 'Leaver'

Below is the data model.

There are filters for FFiscal Year (Finished Fiscal Year) and SFiscal Year (Starting Fiscal Year).

We have a chart to show retention %

Naturally when an FFiscal Year is selected, retention is 0% - I don't think we can get away from that, as we'd be filtering on Finish Date.

However if we select SFiscal Year, we get the correct number of employees that are still employed (Great!) but it doesn't factor in those that are STILL employed, that started before the selected SFiscal Year.

Existing expressions are simply

=Count(Distinct(LINK_Employee))

=Count(distinct {<EmployeeStatus={"*Employee*"}>}LINK_Employee)

Count(distinct {<EmployeeStatus={"*Employee*"}>}LINK_Employee) / Count(distinct LINK_Employee)

Any guidance greatly appreciated.

Chanty4u · ‎2025-02-12

Modify like this

Count(DISTINCT {<

EmployeeStatus={"*Employee*"},

SFiscalYear=,

StartDate={"<= $(=Max(SFiscalYear))"}

>} LINK_Employee)

Oggy172 · ‎2025-02-13

Hi @Chanty4u

That doesn't appear to work. The SFiscalYear is actually [SFiscal Year]

However, the expression Editor doesn;t return the max date

I'm using:

Count(DISTINCT {<  

    EmployeeStatus={"*Employee*"},  

    [SFiscal Year]=, 

    StartDate={"<= $(=Max([SFiscal Year]))"}

>} LINK_Employee)

diegozecchini · ‎2025-02-13

Hi!
what if you count:

firstly employes who were employed at the beginning of the selected SFiscal Year and employees who are still employed at the end of the period?

employees who were already employed before or during the selected SFiscal Year

Count(DISTINCT {< SFiscalYear =, FFiscalYear = >} LINK_Employee)

employees who still have EmployeeStatus = "Employee", meaning they haven’t left

Count(DISTINCT {< EmployeeStatus = {"Employee"}, SFiscalYear = >} LINK_Employee)

retention formula would be

Count(DISTINCT {< EmployeeStatus = {"Employee"}, SFiscalYear = >} LINK_Employee)
/
Count(DISTINCT {< SFiscalYear =, FFiscalYear = >} LINK_Employee)

if correct by selecting SFiscal Year should correctly count both new hires and existing employees still employed while employees who left during or after the period should be excluded from the numerator

Script and Expressions