I have data about employees, and the hours they worked at several jobs. Now i have created a flag to check whether an employee is still active in the data load script.
In a table, I want to show the active employees with some additional information, such as their function.
However, I run in to problems when I want to show the hours worked over all jobs, while simultaneously showing job specific information such as the function, in a different column. I can get the total number of hours worked over all jobs, correctly with the following calculated dimension:
Aggr(if(active_flag = 1,employee_id,Null()),employee_id) , but this deletes information about the function if a employee has had multiple jobs.
If i use the following calculated dimension, the function information is still available but the hours worked are incorrect:
if(start_date < Today() and (isnull(end_date) OR end_date > Today()),employee_id,Null())
Where the logic in the if statement is the same as for the active_flag in the load script.
I have tried using the following expression for the total number of hours worked for a specific employee but it does not work:
The goal that I want to achieve is the following table:
active employees, function of current job, total hours worked over all jobs