Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

sietse_romijn
New Contributor

Calculated dimension hides information

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.

1 Solution

Accepted Solutions
dwforest
Valued Contributor

Re: Calculated dimension hides information

if you want total hours by employee use:

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

3 Replies
dwforest
Valued Contributor

Re: Calculated dimension hides information

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

sietse_romijn
New Contributor

Re: Calculated dimension hides information

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

dwforest
Valued Contributor

Re: Calculated dimension hides information

if you want total hours by employee use:

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