Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hola,
Tengo los siguientes datos:
Employee | Nivel | Fecha |
Adan | Avanzado | 01/01/2021 |
Adan | Avanzado | 01/02/2021 |
Adan | Avanzado | 01/07/2021 |
Adan | Básico | 01/09/2021 |
Angel | Básico | 05/05/2021 |
Angel | Avanzado | 07/05/2021 |
Angel | Intermedio | 08/09/2021 |
Angel | Avanzado | 10/11/2021 |
Angel | Avanzado | 11/11/2021 |
Luz | Avanzado | 03/04/2021 |
Luz | Avanzado | 06/08/2021 |
y lo que necesito obtener es una tabla en donde me muestre solamente el primer y último Nivel que obtuvieron basado en la fecha. Pero no se ocurre algo que me funcione, me podrían dar ideas de como formular la dimensión para la tabla por favor.
Gracias!
Front end solution
Dimension = Employee , Date
Measure = if(max(total <Employee> Date)=Date,'LastestLevel'
,if(min(total <Employee> Date)=Date,'InitialLevel'))
Under Data Handling >> uncheck "Include zero Values"
Output
Script solution:
raw:
Load Employee,Level,date#(Date,'MM/DD/YYYY') as Date Inline [
Employee,Level,Date
Adan,Advanced,01/01/2021
Adan,Advanced,01/02/2021
Adan,Advanced,01/07/2021
Adan,Basic,01/09/2021
Angel,Basic,05/05/2021
Angel,Advanced,07/05/2021
Angel,Intermediate,08/09/2021
Angel,Advanced,10/11/2021
Angel,Advanced,11/11/2021
Luz,Advanced,03/04/2021
Luz,Advanced,06/08/2021
];
Level:
Load
Employee
,Max(Date) as Date
,'LatestLevel' as LevelType
Resident raw
Group by Employee;
Concatenate(Level)
Load
Employee
,Min(Date) as Date
,'InitialLevel' as LevelType
Resident raw
Group by Employee;
Uncheck "Include null values" for column LevelType
Output
Front end solution
Dimension = Employee , Date
Measure = if(max(total <Employee> Date)=Date,'LastestLevel'
,if(min(total <Employee> Date)=Date,'InitialLevel'))
Under Data Handling >> uncheck "Include zero Values"
Output
Thank you!!