Creating an employee tenure chart

[Newbie777]

Hello,

I am trying to create employees' tenure chart which looks like a gantt chart.  I am following the instruction on YouTube for this, and I am having some difficulties.  

My employee table shows hiring dates and leaving dates and employees change their department sometimes, and I want to also reflect department changes after I manage to solve the initial issue I am having difficulties with.

The first issue I'd like to solve is that the current employees have leaving date field blank, so in order to properly calculate the current employees' tenure (not just only ex-employees who have leaving date information in the field), I created the following measure in the horizontal bar chart.  There is no error message in the measure formula, but my tenure chart does not show any tenure length lines for current employees.  I'd appreciate it if anyone can point to me in the right direction of what I am doing wrong, as I am not familiar with Qlik Sense measure formula's behavior.  

Thank you very much for your help!

[rwunderlich · Accepted solution]

Is it possible that your empty leave dates are not blank, but null? Or that they have some spaces in there? In either case, perhaps a more inclusive way to write your measure would be:

Alt([Leave Date],  today(0)) - [HR Changes.Hiring date]

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[Newbie777]

Thanks for your advice.  I will try that formula, (after the Qlik Sense hub service is back again as it seems to be doing some kind of maintenance at the moment).  

Coming from Power BI user background, I am pleasantly surprised to find out that I can put naked columns into an if formula without wrapping them up with aggregating functions like max, min, and sum.  This formula behavior is more intuitive for beginner users than the formula language of Power BI. 😀

[Newbie777]

I just copied the formula and tweaked it a bit to get rid of the error message and the visualization came out perfectly.  Thanks a lot.