Yearfrac function in Qlik Sense

[Newbie777]

Hi 

I am calculating employee service length which respects time dimension, so that if past date is selected in a chart, the service length is reduced accordingly for the length at that point in time.  I am currently dividing the number of days at a point in time minus hiring date by 365 in order to convert the number of days into number of years in decimal, but as the number of days in a year varies between year to year with leap year having 366 days and normal years having 365, the calculation below is not correct.  Please could you let me know an equivalent function with Yearfrac in Excel so that I can accurately calculate the employee service years? 

Thank a lot for your help.

[marcus_sommer]

I think you need to transfer the fixed division of 365 from the outside to the inside and there you could consider on the employee-level which years are included to branch for the appropriate division-factor.

It sounds complicated and I think it is. Personally I would probably go with a quite different approach and pre-calculating the most things already within the datamodel. There are various approaches possible, like adding various day-counter within the master-calendar (calendar-days for each year + over multiple years, working-days, offset-days to n KPI's, ...) or creating with an intervalmatch-approach a table which contained for each employee + calendar-day a counter, surely some more possibilities ...

Just for this task it may sound like a lot of efforts but quite probably there are many other similar calculations and doing everything within the UI with such complex expression - multiple if-loops + aggr() - could become much more complex as a script-solution and by larger datasets it will be also much faster.

- Marcus

[Newbie777]

Thanks for your response. I take it that there are no yearfrac function in Qlik Sense at this moment, and I'd like to vote for a creation of such function in the future. 

[marcus_sommer]

Take a look on daynumberofyear() if it does want you want.

- Marcus

[Newbie777]

Thanks for letting me know about this function.  

The explanation in Qlik Sense website says "The function always uses years based on 366 days.", but I much prefer to use this kind of function than the absolute value 365, so I've switched it with this function.  

Thank you & best regards,