5 Replies Latest reply: Aug 25, 2015 1:51 PM by Sasidhar Parupudi RSS

    Average employment tenure - 'Years Month Days' format

      Hello, Qlik Community.


      Novice scripter here so thanks for the help!


      I'm trying to calculate the average employment tenure, for a selected group of employees, in a text box.  I'm looking to have the results displayed as "Average Tenure is [X] Years, [Y] Months and [Z] Days."


      I have been able to calculate the total number of days and average number of days, by creating a variable in the Load script as:  (today(1) - [Hire Date]) as TenureDays.

      I then used the expressions 'Rep(s) Tenure = ' & num(Sum(TenureDays),'#,##0') & ' Days' for Total Tenure in Days


      "Average tenure is ' & num((Sum(TenureDays))/Count([Employee Name]),'#,##0') & ' days.' for Average Tenure in Days.


      Finally, I have been able to get the Total Tenure, expressed in Years Months Days by using  div(Sum(TenureDays),365) &  ' Years ' & div(mod(Sum(TenureDays),365),30) & ' months ' & mod(mod(Sum(TenureDays),365),30) & ' days'


      However, I cannot find a valid expression for the Average Tenure in the format Years Months Days.


      Assuming the total days is 510, by using the expression div(num((Sum(TenureDays))/Count([Employee Name]),'#,##0'),365) & ' Years ' , I can get a result of 1 Years.


      However, I get an Error in Expression, when trying to also get the Months and Days,  by using: div(num((Sum(TenureDays))/Count([Employee Name]),'#,##0'),365) & ' Years ' & div(mod(num(Sum(today(1) - [Hire Date]),365),30) & ' months ' & mod(mod(num(Sum(today(1) - [Hire Date]),365),30) & ' days'


      Any suggestions would be most helpful.  Hope I explained this OK.