Calculate 10-year seniority period

[#1]

Hi All,

there are two tables: previous employment (outside company) and current employment (in company). Previous employment table stores data of seniority length (e.g. 8 years 4 months 2 days) only, no dates are given. Current employment table stores dates of beginnings and ends of employment (e.g. StartDate: 2015-05-06, EndDate: 2018-12-31). I need to add the difference between 10 years and seniority length (e.g. 1 year 7 months XX days) to StartDate to find the the date of 10-year seniority period to raise holiday allowance from the given date. Months have calendar number of days, not 30/31 - in the above example months would be from May 2015 to Dec 2016. Preferably, I'd like to calculate the date in the script, not charts.

PreviousEmployment:

EmpId	CompanyId	Years	Months	Days
1	3	9	4	2
2	10	5	1	19

CurrentEmployment

EmpId	StartDate	EndDate
1	2016-01-01	2018-12-31
2	2007-02-04	2020-12-31

ExpectedResults

EmpId	10YearSeniorityDate
1	2016-08-28
2	2011-12-16

Regards,

Przemek

[sunny_talwar]

This gets you very close. I think the difference here is due to use of 365 days vs 366 days. Are you looking for the precise dates using the correct number of days?

Table:

LOAD EmpId,

    CompanyId,

    Years,

    Months,

    Days,

    (10 - (Years + Months/12 + Days/365)) as DaysLeft

FROM

[https://community.qlik.com/thread/208764]

(html, codepage is 1252, embedded labels, table is @1);

Join (Table)

LOAD EmpId,

    StartDate,

    EndDate

FROM

[https://community.qlik.com/thread/208764]

(html, codepage is 1252, embedded labels, table is @2);

FinalTable:

LOAD *,

  Date(Floor(StartDate + (DaysLeft * 365)), 'YYYY-MM-DD') as [10YearSeniorityDate]

Resident Table;

DROP Table Table;

[#3]

Hi Sunny,

thank you form your reply. Yes, I'm looking for precise date, cannot assume 365 days a year.

Regards,

Przemek

[tresesco]

May be like:

Table:

LOAD EmpId,

    CompanyId,

    Years,

    Months,

    Days

FROM

[https://community.qlik.com/thread/208764]

(html, codepage is 1252, embedded labels, table is @1);

Join (Table)

LOAD EmpId,

    StartDate,

    EndDate

FROM

[https://community.qlik.com/thread/208764]

(html, codepage is 1252, embedded labels, table is @2);

FinalTable:

Load

  *,

AddYears(EmploymentStarted,10) as [10YearSeniorityDate];

LOAD *,

  Date(Addmonths(Addyears(StartDate,-Years),-Months)-Days) as EmploymentStarted

Resident Table;

DROP Table Table;