Clasificar a los trabajadores en función de los turnos trabajados

[Anonymous]

¡Hola a todos! Necesito ayuda con esto: Tengo que categorizar a los trabajadores en función de su estacionalidad. El modelo es muy simple, lo ilustraré en la mesa:

Id	Total_Shifts	Mes	año	Year_Month
1	12	12	2019	2019-12
2	14	12	2019	2019-12
3	15	12	2019	2019-12
1	33	01	2020	2020-01
2	24	01	2020	2020-01
3	3	01	2020	2020-01

Por lo tanto, tengo cada trabajador con su identificación y el total de turnos que trabajaron cada mes del año dado (la base de datos cubre 3 años con cientos de trabajadores).

Tengo que agrupar a todos en base a esto:

Trabajador ocasional: cada trabajador que trabaja de 15 a 25 turnos al año.

Trabajador estacional: cada trabajador que trabaja un promedio de 5,5 a 20,5 turnos por mes, pero sólo si trabajó durante cuatro meses o menos del año dado.

Trabajador esporádico: cada trabajador que trabaja un promedio de 5,5 a 20,5 turnos por mes.

Regular: de 15 a 25 turnos por mes.

Los otros solo encajan en la categoría "Otros".

Creo que tengo que hacer esto con una columna calculada y un montón de ifs.
Si tuviera que hacer una medida de esto supongo que no sería tan difícil porque el gráfico en sí mismo agrupa los valores, pero no sé cómo puedo hacer para agrupar estos cálculos año tras año como sucede con el trabajador ocasional o cómo puedo usar los meses de mudanza para el Trabajador de temporada (el creo que es el más difícil).

Después de categorizar, tengo que calcular cuántas personas encajan en cada categoría y calcular los turnos promedio para cada categoría.

Le agradecería mucho toda la ayuda que pueda darme,

Gracias por su tiempo,
Saludos
Gabriela

[edhans]

No creo que nos hayas dado suficiente información para hacer esto, pero estoy de acuerdo, por mucho que odie las columnas calculadas, esto es lo que necesitas. Y usted quiere utilizar SWITCH() aquí.

Aquí está un ejemplo de cómo funcionaría esto:

Category = 
VAR CurrentID = [ID]
VAR AverageShifts = 
    AVERAGEX(
        FILTER(
            'Table',
            'Table'[ID]=CurrentID
        ),
        'Table'[Total_Shifts]
    )
RETURN
SWITCH(
    TRUE(),
    [Total_Shifts] >= 15 && [Total_Shifts] <= 25, "Occasional",
    AverageShifts >= 5.5 && AverageShifts <= 20.5, "Seasonal"
)

Esto devuelve lo siguiente:

Ignore la columna "Promedio de desplazamiento". Lo usé para hacer todo entre la primera lógica VAR y la lógica RETURN que luego copié y pasioné en la medida Categoría como se muestra arriba. Pero puede ver que el #1 de trabajadores tiene 22,5 turnos promedio, y el trabajador 2 tiene 19, etc.

Pero no vi cómo debía determinar cuántos meses en un año trabajaron con esos datos. Pero puede agregar más condiciones a cada línea SWITCH como lo haría en Excel. Use && para AND, y . para EL, y agrupar con paréntesis según sea necesario. ESTE && (QUE SOMETHIGN ELSE) por ejemplo

Espero que eso sea suficiente para empezar.

[Anonymous]

Hola @edhans
He probado su solución y aunque el código funciona no se ajusta del todo a lo que necesito: el trabajador ocasional es el que funciona de 15 a 25 turnos al año; y el trabajador de temporada es el que trabajó esa cantidad de turnos promedio, pero durante cuatro meses o menos, por ejemplo, si un trabajador trabajaba 6 turnos cada mes del año sería esporádico (AVG-6), pero si trabajaba 20 turnos por sólo cuatro meses sería estacional (AVG 20*4/12 a 6,6) sería estacional. La base de datos considera más de un año, y cientos de trabajadores, por lo que tengo que tener en cuenta los años. Comparto una muestra de DB de muestra

Saludos
Gabriela

[edhans]

Ok, creo que tengo un mejor entendimiento, y lo haría en Power Query. Un simple SWITCH() no lo cortará. Una más compleja lo haría, pero necesita más transformaciones de tabla. Gracias por los datos. Insinosamente útil entender el problema.

Vea mi archivo PBIX aquí. Está vinculado a sus datos, por lo que tendrá que cambiarlo para que apunte a la "BD Sample.xlsx" en su ordenador a través del icono Orígenes de datos, Cambiar origen elemento.

Esto es lo que hice:

1. Quería verificar que no había datos duplicados. Lo hubo, así que lo agrupé. Puede ver estos datos en Power Query denominado [Hoja1 DUPLICATES] - cada uno de ellos tiene 2 registros con el mismo año y mes. Agregué los turnos totales juntos. Esto era importante para el siguiente paso.
   1. 
   2. Si eso es incorrecto, debe corregir los datos o cambiar el funcionamiento de mi paso "Filas agrupadas para combinar duplicados" en la consulta [Hoja1].
2. Luego hice un segundo grupo por ID y año solamente, agregando el recuento de registros como "Mes" y Total Shifts. El recuento funciona porque ahora cada ID/Mes/Año es único, por lo que la agrupación por ID/Mes y el recuento de cuántos registros hay el número de meses.
   1. 
3. Luego agregué una columna "Cambios promedio" que es sólo Total de Turnos / Meses. Ahora tengo turnos promedio por mes. Redondeé arbitrariamente a 2 decimales. Haz lo que quieras allí.
4. Luego agregué la columna Estado. Aquí es donde está el trabajo.

if [Total Shifts] >= 15 and [Total Shifts] <= 25 then "Occasional" else
if [Months Worked] =4 and [Average Shifts] >= 5.5 and [Average Shifts] <= 20.5 then "Seasonal" else
if [Months Worked] > 4 and [Average Shifts] >= 5.5 and [Average Shifts] <= 20.5 then "Sporadic" else
if [Average Shifts] >= 15 and [Average Shifts] <=25 then "Regular" else 
"Other"

Pasa por las 4 sentencias IF, y si ninguna coincide, el valor predeterminado es "Otros".

Si mi lógica es incorrecta porque he entendido mal sus requisitos, debería poder modificar ese código. La forma en que lo hice es, como entendí sus requisitos:

1. si entre 15-25 turnos por año, "Ocasional"
2. si los meses trabajados 4 y los turnos medios eran entre 5,5 y 20,5, "Estacional"
3. si los turnos fueran más de 4 por año y el promedio estuviera entre 5,5 y 20,5, "Esporádico"
4. Si los turnos fueran un promedio entre 15 y 25 por mes, "Regular"
5. Todo lo demás se cae como "Otros"

Puede cambiar esa lógica y agregar una línea if/then/else adicional antes de la "otra". Sólo tenga cuidado de no hacer que ninguno se superponga. Por ejemplo, si ha creado una nueva 5a línea de "Si los meses trabajados a r y los cambios medios entre 15 y 25" que no funcionaría como regla #2 los que se encuentran en el rango 5.5-20.5 y la nueva regla #5 solo los conseguiría entre 20,5 y 25.

Si necesita hacer algo de lógica OR, simplemente reemplace y con o, y puede usar paréntesis.

si ([Meses trabajados] < 3 o [Meses trabajados > 12) y [Total de turnos] < 25

como ejemplo primero devolvería TRUE si los meses fueran 1-3 o > 12, y luego mirarían los turnos totales.

Retroceda cualquier pregunta.

Nota: Puede hacer esto como lo haría en Excel también tal vez. Haga una columna por critera, luego, cuando haya terminado, combine esas lógicas de columna en una sola fórmula, pero la lógica if/then/else es bastante fácil de construir, pero tiene que tener cuidado con las superposiciones.

[Anonymous]

Hola @edhans !! Muchas gracias por la dedicación y por explicar paso a paso, no sabía que podías hacer todo esto en Power Query parece mucho más simple.

Traté de hacerlo en DAX mezclando un montón de otras fórmulas que encontré y obtuve una solución con una columna calculada, aquí está:

Category = 
VAR ANUAL_SUM = 
CALCULATE(SUM('DAX_Test'[Total Shifts]),
        FILTER(ALLEXCEPT('DAX_Test', 'DAX_Test'[Year]),
        'DAX_Test'[ID]=EARLIER('DAX_Test'[ID])
            )
)

VAR ANUAL_AVG = ANUAL_SUM/12

VAR WORKEDMONTHS =
CALCULATE(DISTINCTCOUNT('DAX_Test'[Month]),
        FILTER(ALLEXCEPT('DAX_Test','DAX_Test'[Year]),
        'DAX_Test'[ID]=EARLIER('DAX_Test'[ID])
            )
)

RETURN
    /*Occasional: 15 to 25 shifts per year*/
    if(
        (ANUAL_SUM <= 25 && ANUAL_SUM >= 15) ,
            "Occasional" ,

                /*Seasonal: average between 5,5 to 20,5 per month on 4 months or less*/
                if(
                    (ANUAL_AVG <= 20,5 && ANUAL_AVG >= 5,5) && (WORKEDMONTHS <= 4 ) ,
                    "Seasonal",

                        /*Sporadic: 5,5 to 20,5 shifts per month*/
                        if(
                            ANUAL_AVG <= 20,5 && ANUAL_AVG >= 5,5 ,
                            "Sporadic" ,

                                /*Regular: 9 to 24 shifts per month*/
                                if(
                                    (WORKEDMONTHS >= 10) && (MAX('DAX_Test'[Total Shifts]) <= 24 && MIN('DAX_Test'[Total Shifts]) >= 9) ,
                                    "Regular" ,
                                                                               
                                    "Other"
                                )
                        )
                )
    )

Validé cada categoría con su solución y la mía y obtuve algunas diferencias que cambié en Power Query y finalmente conseguí una combinación perfecta para Sporadic, Occasional y Seasonal, pero no para Regular. Las diferencias fueron:
- El promedio que tengo que calcular no tiene en cuenta cuántos meses trabajó la persona, siempre considera 12. Esto cambié y trabajé bien.

- El trabajador regular tiene que trabajar de 15 a 25 turnos por mes cada mes del año, excepto dos para considerar las vacaciones, por lo que soluciono que con un "y 'meses trabajados' > 10", pero no podía averiguar cómo poner que el rango para cada mes debe estar entre 15 a 25, eso es diferente de la media y yo no sabría cómo agregarlo en Power Query. En DAX puse un MIN y un MAX, pero no estoy seguro de que lo resuelva.
¿Podrías por favor echar un vistazo a esto para ver si podemos llegar a una coincidencia perfecta en Regular también? Bueno, parece por los datos que será casi imposible para un trabajador obtener un estado regular, dadas tantas reglas, pero los datos de un trabajador podrían ser cambiar para ver si esto funciona.
Aquí está el enlace para el archivo pbix

Gracias por su tiempo,
Saludos
Gabriela

[edhans]

Sí, puedes hacer esto, pero tuve que pensarlo. Vuelva a ver mi archivo PBIX y examine la consulta [Hoja1]. El stil original en [Hoja1 Original]

Básicamente tuve que averiguar quién era Regular (15-25 turnos) y trabajó 10+ meses de esa manera.

1. Empecé con la consulta original.
2. Después del paso "Filas agrupadas para combinar duplicados" He añadido 4 pasos:
   1. Primero agregué una columna simple llamada Estado de temp que si [Total Shifts] está entre 15 y 25, categorizando como Regular, de lo contrario null.
   2. Luego modifiqué el paso"Agrupar filas por ID y Año"para agregar una columna adicional, [Todas las filas] - está en rojo a continuación
      1. 
      2. Esto devuelve una columna con una tabla en ella. En el que he mostrado a continuación, tiene 9 registros con "Regular" en él, y 3 que son nulos. Este registro en particular fallará la siguiente prueba ya que necesitamos 10-12 sin null.
      3. 
   3. Se ha añadido el paso"Añadida lista filtrada de filas <> null"que devuelve los filtros del paso anterior.
      1. •Table.SelectRows([Todas las filas], cada una [Estado temp] <> null)
      2. Puede ver en el 2o registro, esto devuelve una tabla anidada de solo dos registros. Esto también fallará
      3. 
   4. A continuación agregué una fórmula para contar esas filas filtradas
      1. • Table.RowCount([Regular Filter Out Null])
      2. Ahora sé 100% seguro de quién trabajó 10+ meses cada año entre 15-25 turnos
3. Luego cambié el paso que agrega el estado para que sea la fórmula siguiente. La asignación "Regular" estaba cerca del final. Ahora es lo primero y solo comprueba el recuento de filas de la tabla filtrada.

if [All Rows Count] >= 10 then "Regular" else 
if [Total Shifts] >= 15 and [Total Shifts] <= 25 then "Occasional" else
if [Months Worked] <= 4 and [Average Shifts] >= 5.5 and [Average Shifts] <= 20.5 then "Seasonal" else
if [Months Worked] > 4 and [Average Shifts] >= 5.5 and [Average Shifts] <= 20.5 then "Sporadic" else
"Other"

El último paso fue mantener sólo las columnas que necesitaba y eliminar toda esa basura intermedia.

Felicidades apreciados en este si es útil. La consulta fue bastante simple, pero tuve que fideos a través de la lógica de cómo obtener un recuento confiable ya que Average no iba a asegurar que cumplía con sus requisitos.