12 Replies Latest reply on Oct 25, 2017 11:22 PM by Gavin Wong

    Calculating a the start date within an "array"

    Gavin Wong

      Hi community

       

      in an exercise to review long service, I am reviewing all user ids in an organisation. A user id would have switched depts within the organisation in their tenure with the company.

      So to calculate the years of service, we are looking at a single user id that has multiple departments changed and is still an active employee (end date 31 Dec 2099)

      Also, he has 2 roles, a role 3 and role 4

       

      So if an employee has several end dates, then the years of service would start from the min start date until 31st Dec 2017.

       

      Eg,

      User ID 2050 in dept code GV45

      Min Start Date is 1st Aug 2009, ended his role on 1st March 2012

      Started at another dept code YM43

      Min start date is 1st Dec 2012 and is still active as of now

       

      The assumption is that if the end date is 31 Dec 2099, this person is active.

      So the years of service is

       

      1st Aug 2009 - 31st Dec 2017

       

      How can i get tableau to look at that ?

      I cant seem to get LOD to work.

       

      I attached the workbook as illustration.