Set Analysis for year less or greater than another year

[lailarhc]

Hello.
I have a field called [Data de Entrada.autoCalendar.Year] that represents the year an employee was hired and  has begun working.

I have another field called [Data.autoCalendar.Year] that represents the year a course was taken by an employee.

I'm trying to count in a KPI how many employees were active in a given [Data.autoCalendar.Year] year that was selected.

Here's the expression I'm using for the KPI:

=Count({$ <[Data de Entrada.autoCalendar.Year] = {"<=$(=$(Min(GetFieldSelections([Data.autoCalendar.Year]))))"}>} distinct [Nome do servidor])

Where [Nome do servidor] represents the employee's names.

But the KPI is stuck showing me 0 (there are 48 distinct employees in total):

So I tried to simplify the expression and try to only count the employees that began working before 2005. Here's the expression I tried using:

=Count({$ <[Data de Entrada.autoCalendar.Year] = {"<=2005"}>} distinct [Nome do servidor])

It didn't work. KPI still shows 0.

So I tried this:
=Count({$ <[Data de Entrada.autoCalendar.Year] = {"=2005"}>} distinct [Nome do servidor])

And the KPI wrongfully displays the number 48. It's counting all the employees, not only those that began working on 2005.

How can I make it show how many employees are actively working (year they were hired <= selected year) in a given year?

Thank you

[abhijitnalekar]

Hi @lailarhc ,

Can you please share some sample data?

[lailarhc]

Sure.

Here's a table with all the columns I'm working with.

Take note that Carga Horária is a measure with the expression Sum([Carga Horária]) instead and it represents the total duration of a course in hours (some course happened over a period of days, but I count those as a single course).

Actually, here's the relationship between each column's name and the field name being used:

Nome do Servidor (employee name) = [Nome do servidor]

Ano (year the course happened) = [Data.autoCalendar.Year]

Mês (month the course happened) = [Data.autoCalendar.Month]

Curso (course name) = [Nome Evento]

Carga Horária (course duration in hours) = Sum([Carga Horária]

Área de Concentração (course category) = [Área de Concentração]

Data Exercício (when the employee began working) = [Data de Entrada.autoCalendar.Year]

[abhijitnalekar]

Hi @lailarhc ,

Please check the below expression.

Count({$ <[Data Exercício] = {"<=$(Coalesce(GetFieldSelections(Ano),max(Ano)))"}>} distinct [Nome do Servidor])

[lailarhc]

It seemed like it worked perfectly at first, but when I select the year ([Data.autoCalendar.Year] or Ano) 2001, it says there's a total of 0 active employees.

But there are actually 5 employees that have begun working ([Data de Entrada.autoCalendar.Year] or just [Data Exercício]) on 1998. And there are 5 employees that have begun working on 2000.

So the KPI should display 10 when the year 2001 is selected.

But I've noticed that there are no data at all for the year 2001, as seen in the image bellow, so maybe it's my table that is the problem.

But there's a second problem. When I select the year ([Data.autoCalendar.Year] or Ano) 2002, the KPI correctly displays 10 active employees, as seen bellow:

But when I select the year ([Data.autoCalendar.Year] or Ano) 2003, the KPI displays the number 9, when there should still be 10 people actively working (since I'm only working with active employees, none of them have left or retired, the only difference being when they've begun working).

The employee called MONICA LESSA SANTOS  has not taken any courses in 2003, so she's not being counted.

Here's a table where the columns' expressions are for Ano, Servidores com Curso and Servidores Ativos, respectively:

[Data.autoCalendar.Year]

Count(distinct [Nome do servidor])

and  =Count({$ <[Data de Entrada.autoCalendar.Year] = {"<=$(Coalesce(GetFieldSelections([Data.autoCalendar.Year]), Max([Data.autoCalendar.Year])))"}>} distinct [Nome do servidor])

Which means that the expression is only counting employees who have taken courses, not active employees regardless of whether they have taken courses or not.

I have two different tables that I'm getting my data from. One is a table with all the data regarding the courses that were taken and when. The column [Data.autoCalendar.Year] belongs to this table.

And I have a second table with all my employees' data. The column [Nome do servidor] belongs to this table.

Correct me if I'm wrong, but I think I need to somehow include possible null values when using the column [Data.autoCalendar.Year]  so I can count employees based on the date they begun working ([Data de Entrada.autoCalendar.Year] ) even if they have not taken any courses.

[lailarhc]

@abhijitnalekar , do you think you could help me?