3 Replies Latest reply: Apr 13, 2018 1:13 PM by David Forest RSS

    Calculated dimension hides information

    Sietse Romijn

      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.