How to subtract in set analysis

vmoreno2605 — Mon, 05 Nov 2018 17:06:58 GMT

Hi everyone! I need your help.

I have a formula that gets me the quantity of active employees.

This formula allows me to see the quantity of active employees through the time

This is the formula:

Count({<FechaNum={"<$(vFechaInicioAño)"},HireTerm={'H'}>}Distinct EMPLOYEE) -

Count({<FechaNum={"<$(vFechaInicioAño)"},HireTerm={'T'}>}Distinct EMPLOYEE) +

Count({<FechaNum={"<=$(vFecha)>=$(vFechaInicioAño)"},HireTerm={'H'}>}Distinct EMPLOYEE) -

Count({<FechaNum={"<=$(vFecha)>=$(vFechaInicioAño)"},HireTerm={'T'}>}Distinct EMPLOYEE)

$(vFecha)=This is the date that i'm watching now

$(vFechaInicioAño)=This is January 1st from the year of $(vFecha)

I want to filter by amount of time the employee has been active, for example: 1 week, 2-3 months, 1-2 years...

There is a field called HireDate, this field contains the date when the employee was hired.

NOTE: Because of business rule, this is the only way(this formula) i'm able to get how many active people we have, but now i want them like this :

This table is the result for the current date, but it only works for today, i want to be able to see this info through the time.

I've been trying to use if like this example:

if($(vFecha)-HireDate<=90,

Count({<FechaNum={"<$(vFechaInicioAño)"},HireTerm={'H'}>}Distinct EMPLOYEE) -

Count({<FechaNum={"<$(vFechaInicioAño)"},HireTerm={'T'}>}Distinct EMPLOYEE) +

Count({<FechaNum={"<=$(vFecha)>=$(vFechaInicioAño)"},HireTerm={'H'}>}Distinct EMPLOYEE) -

Count({<FechaNum={"<=$(vFecha)>=$(vFechaInicioAño)"},HireTerm={'T'}>}Distinct EMPLOYEE))

This does not work, but you can see what i try to do in case you don;t understand my english.

I'd appreciate your help.

Thanks

Re: How to subtract in set analysis

vmoreno2605 — Mon, 05 Nov 2018 17:48:43 GMT

THIS WORKS! but i still need it like <=90 and >180, it only works with <=90

Count({<FechaNum={"<$(vFechaInicioAño)"},HireTerm={'H'},HireDate={"=$(vFecha)-HireDate<=90"}>}Distinct EMPLOYEE) -

Count({<FechaNum={"<$(vFechaInicioAño)"},HireTerm={'T'},HireDate={"=$(vFecha)-HireDate<=90"}>}Distinct EMPLOYEE) +

Count({<FechaNum={"<=$(vFecha)>=$(vFechaInicioAño)"},HireTerm={'H'},HireDate={"=$(vFecha)-HireDate<=90"}>}Distinct EMPLOYEE) -

Count({<FechaNum={"<=$(vFecha)>=$(vFechaInicioAño)"},HireTerm={'T'},HireDate={"=$(vFecha)-HireDate<=90"}>}Distinct EMPLOYEE)

vmoreno2605 — Mon, 05 Nov 2018 17:50:26 GMT

Sorry, i need it like >90 and <=180 Thanks