Calculated dimension hides information

Anonymous — Tue, 10 Apr 2018 14:12:58 GMT

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.

Re: Calculated dimension hides information

dwforest — Tue, 10 Apr 2018 18:17:41 GMT

Sum({$<active_flag={1}>} hours) will aggregate to the level of other dimensions in the table

Re: Calculated dimension hides information

Anonymous — Fri, 13 Apr 2018 06:35:11 GMT

This still only computes the hours worked over the currently active job, as opposed to the total number of hours worked over all jobs.

Re: Calculated dimension hides information

dwforest — Fri, 13 Apr 2018 17:13:39 GMT

if you want total hours by employee use:

Sum({$<active_flag={1}>} TOTAL <employee_id> hours)

topic Re: Calculated dimension hides information in App Development

Calculated dimension hides information

Re: Calculated dimension hides information

Re: Calculated dimension hides information

Re: Calculated dimension hides information