Hi,
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:
Sum(Aggr(sum(hours),job_id,employee_id))
The goal that I want to achieve is the following table:
active employees, function of current job, total hours worked over all jobs
My data model looks like:
Employee:
employee_id
active_flag
Hours:
employee_id
job_id
hours
Jobs:
job_id
function
start_date
end_date
Any help would be much appreciated.
