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

    Average employment tenure - 'Years Month Days' format

    Kevin Bertoia

      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

      and

      "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.


      Thanks
      Kevin