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

# Calculating a the start date within an "array"

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.

• ###### 1. Re: Calculating a the start date within an "array"

Hi Gavin,

Find may approach as reference below and stored in attached workbook version 10.4 located in the original thread.

1. Active / In Active: if {fixed [Userid]:max([Enddate])}=#31-12-2099# then "Active" else "In-Active" END

2 . Min Startdate: {fixed[Userid]:min( [Startdate])}

3. Max Startdate: {fixed[Userid]:max( [Startdate])}

4. Years in Service: DATEDIFF('year',[Min StartDate],[Max StartDate ])

5. Sort based on Years in Service

Regards,

Norbert

• ###### 2. Re: Calculating a the start date within an "array"

Hi

Thanks. But why are there 0 years when user id is active?

Gavin

1 of 1 people found this helpful
• ###### 3. Re: Calculating a the start date within an "array"

Hi Gavin,

Apologies, a "glitch" from my side;) Find my updated approach attached

1. Calculation Month in Service: DATEDIFF('month',[Min StartDate],today())

2. Calculation Years in Service  DATEDIFF('year',[Min StartDate],today())

Regards,

Norbert

1 of 1 people found this helpful
• ###### 4. Re: Calculating a the start date within an "array"

Hi norbert

one more thing though...

the problem I have when the end date of 1 dept and the start date of another dept are not immediate or concurrent. Most of which are “overlapped” if you know what I mean.

This user as seen in the screen grab so happened that the end date is 30th April 2014 and before he ended with dept HJ59, he started on 1st march 2017 at dept KY81 while still at HJ59.

IF, this user who left HJ59 on 30 April 2017, started work at KY81 on say 10th May 2017, again theres a break!

I am finding a way to discount away breaks like these.

My problem is have a proper calculation of determining the proper years of service that an employee has in the company and that it has to be fair.

This user should be 7.257yrs instead of 9.003yrs after discounting the break between 2010-2012.

Agree?

• ###### 5. Re: Calculating a the start date within an "array"

Hi Gavin

userID 1929 is active at two departments KY81 and YM43

He started at 1-1-2009 at DM97 an is 8,75 years in service.

Not sure if I understand where the 9.003 comes from

Regards,

Norbert

• ###### 6. Re: Calculating a the start date within an "array"

Hi Norbert

9.003 years is taken from the minimum start date which is 1st Jan 2009 till 31st Dec 2017 (as this user is still active). User stayed in DM97 until 31st Dec 2012.

Then there is a gap from then until 28th Sept 2012 where he begins in HJ59 on 28th Sept 2012.

Then on 1st of March, user began work at KY81 while still working in HJ59 and ended at HJ59 on 30th April 2014.

Then on 26th Aug 2012, user began at YM43 while still working at KY81.

So he is concurrently working in 2 departments. The actual years of service should be from 1st Jan 2009 until 31st Dec 2017 taking away his break from 31st Dec 2010 till 28th Sept 2012 which netts off at 7.257years.

• ###### 7. Re: Calculating a the start date within an "array"

Hi Gavin,

Oke now I understand. You would like to have "netts" stay. I have some idea's but must leave now. I will come back to you later today or on the weekend.

Regards,

Norbert

• ###### 8. Re: Calculating a the start date within an "array"

Hi Gavin,

Upfront not final but just to check so we are going in the right direction

Regards,

Norbert

• ###### 9. Re: Calculating a the start date within an "array"

Hi Norbert

Sorry for the delay in reply.

It looks kinda correct but not understanding how the calculation works.

Shouldn’t it be 7.257 years ? I could be wrong.

But in the 1st line under dept code DM97, how was the 46 months in service calculated ?

What does the rest of the columns look at ?

Am confused.

Thanks. ☺

Gavin

• ###### 10. Re: Calculating a the start date within an "array"

Hi there Norbert

What do the terms actually mean “Brutt”

Gavin

• ###### 11. Re: Calculating a the start date within an "array"

Hi Gavin,

"Brutt" is the time between first day at the company and today. But because did not always work at a department so we have to deduct this amount of time from the "Brutt". Hope it's clear

Have a nice day

Regards,

Norbert

• ###### 12. Re: Calculating a the start date within an "array"

Hi Norbert

Thanks for that. Did you manage to see my previous comment about some clarifications I asked about ?

thanks

Gavin