Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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'
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
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
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
Please See the attachment with sample data
hth
Sasi