Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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 Years, Months and 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

5 Replies
Gysbert_Wassenaar

Why aren't you reusing the TenureDays for the months and days as well?

div(Sum(TenureDays)/Count([Employee Name]),365) &  ' Years ' & div(mod(Sum(TenureDays)/Count([Employee Name]),365),30) & ' months ' & mod(mod(Sum(TenureDays)/Count([Employee Name]),365),30) & ' days'


talk is cheap, supply exceeds demand
sasiparupudi1
Master III
Master III

for calculating days

Interval(today(1) -[Hire Date],'D')

for calculating months

(((year(today(1))*12)+month(today(1))) - (((year([Hire Date])*12)+month([Hire Date]))))

for calculating years

(((year(today(1))*12)+month(today(1))) - (((year([Hire Date])*12)+month([Hire Date]))))/12

hth

Sasi

Not applicable
Author

Hi.


Thanks, Gysbert.


Good call.  Yes, I hadn't changed the Months and Days to use the TenureDays variable; I have now done so.

I used your suggestion and the 'Error in Expression' message went away (awesome!) and it's returning '1 Years' but no value for 'Months' and 'Days' (display is 1 Years Months Days)

Any ideas? 


Thanks very much for the help.

Kevin

Not applicable
Author

Hi, Sasidhar.

Thanks for the response.

I tried to enter the noted script but, while the script was 'OK', it didn't return a result in the app.

Any ideas?

Thanks for your help!

Kevin

sasiparupudi1
Master III
Master III

Please See the attachment with sample data

hth

Sasi