Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
if you want total hours by employee use:
Sum({$<active_flag={1}>} TOTAL <employee_id> hours)
Sum({$<active_flag={1}>} hours) will aggregate to the level of other dimensions in the table
This still only computes the hours worked over the currently active job, as opposed to the total number of hours worked over all jobs.
if you want total hours by employee use:
Sum({$<active_flag={1}>} TOTAL <employee_id> hours)