Employee tenure gantt chart with department changes

[Newbie777]

Hi,

I have created an employee tenure gantt chart using the expression below, but the below expression does not deal with employees who changed departments and have multiple hiring dates.  (I have addressed department change date as 2nd hiring dates).  Please could you let me know how I can reflect in the gantt chart visualization for employees who have multiple hiring dates?  

Thank you & best regards,

[PrashantSangle]

can you explain little bit more using sample data.?

[Newbie777]

Thanks for your response.  I have employee change fact table which unlike the dimension table of employee table, single employee can have multiple appearance such as hiring date, department change date 1 and department change date 2, which are all shown in the hiring date field. 

For the employees who did not switch departments during their tenure, the expression below works perfectly.

However, for employees, who have multiple occurrence in the employee change fact table, the gantt chart does not show their tenure line visualization.  In order to address this issue, I have come up with an analog solution where I manually modified the employee fact table to indicate 1 for employees who appear only once in the fact table and 2 for employees whose appearance is 2nd time in the employee change fact table, and 3 for the employees whose appearance is 3rd time in the employee change fact table and so on.  And then, I modified the expression hoping to see the employees who appear multiple times to be shown in their 1st appearance tenure in the gantt chart.  But the gantt chart still does not show them.  I am not familiar with the behavior of Qlik Sense formulas, but I guess that because those employees appear more than once in the fact table, they are judged not to be 1 because they also have 2 and sometimes 3 under their name, even though I was expecting the formula below to only filter down their 1st appearance, so that I can do the same thing for 2nd and 3rd appearance and add up in the formula below.  I guess instead of if formula, I should be using filter functions, and then add up Change=2, and Change =3 to get the complete tenure visualization.  

I'd appreciate it if you could let me know how I can combine filter function in the if expression above.  

Thank you for your help!

[Newbie777]

I would like to apply filter expression like below, but I am not sure how to fix the expression below to correct syntax. I'd appreciate it if anyone can point me in the right direction.  

[Newbie777]

I'd appreciate it if anyone could let me know how I can combine the set expression:

{<Gender={'F'},Change={'2'}>}

and another expression

Alt(date([Leave date]), today(0)) - Alt([Hiring date], today(0))

in a single expression.  I am not sure how the two above should be combined.  

Thanks a lot for your help.

[Newbie777]

I have been researching about this all afternoon, but I didn't manage to find an answer. The problem is that if employees have more than 1 appearance in the HR change fact table, the gantt chart for them do not become visible even though I try to add set filter condition like below. I understood from my researching on the internet that set has to be included in the aggregate function, so I wrapped the below expression in sum, which is an aggregate function.

Alt(date([Leave date]), today(0)) - Alt([Hiring date], today(0))

sum({<Change={'1'}>}if(Gender='F',Alt(date([Leave date]), today(0)) - Alt([Hiring date], today(0))) )

I guess the reason why the gantt chart doesn't appear for employees who have transferred departments is because instead of filtering, the calculation is applying the condition if it has more than 1 in the Change fields, it will not be included in the calculation.  I'd greatly appreciate it if anyone can point me in the right direction.

[Newbie777]

I'd appreciate it if anyone can let me know how I can filter the table with {<Change={'1'}>} before executing the expression:

if(Gender='F', Alt(date([Leave date]), today(0)) - Alt([Hiring date], today(0)))

Thanks for your help.

[Newbie777]

Hello,

I'd appreciate it if anyone can let me know how I can write a filter expression in the table I want to use if formula for the calculation.  I want to filter the table which I am applying the following calculation before the calculation if performed.  

Please let me know how I can combine filter function like 

{<Gender={'F'},Change={'2'}>}

with the if calculation above when the filter result is not just true or false for an employee like in case of gender, but can have multiple changes for a single employee.  

[Newbie777]

Hi,

Does anyone know if my question regarding filtering the table before applying the calculation can be solved by Aggr() function?  I read that Aggr() function builds a temporary table and I think this could work to solve the problem I am having with respect to filtering the table before applying the calculation.  I'd like to find out if I can combine Aggr() function with the expression below

if(Gender='F', Alt(date([Leave date]), today(0)) - Alt([Hiring date], today(0)))

to apply calculation to a temporary table filtered by {Change={'2'}>}.

Thanks for your help.