Average employment tenure - 'Years Month Days' format

[#1]

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

[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'

[sasiparupudi1]

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

[#4]

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

[#5]

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]

Please See the attachment with sample data

hth

Sasi