10 Replies Latest reply on Nov 17, 2016 6:24 AM by Simon Runc

    LOD calculation help

    Nutan Patel

      Hi Experts,


      I am trying to find the termination date for each each hire record for each employee but not sure whether how to write the LOD calculation for the same.

      In the below image, left side table is actual data table where as right most two columns are expected output of calculated field/LOD calculation. I am attaching data file also.


      Can any one please help me to address this issue ?



      Nutan Patel

        • 1. Re: LOD calculation help
          Simon Runc

          hi Nutan,


          If you only ever have 2 rows for each employee, with HIR & TERM, then


          {FIXED [EmpID]: MAX([Effective Date])}


          Should do the trick.

          • 2. Re: LOD calculation help
            Nutan Patel

            Hi SIMON,


            Thanks for your quick response.!!

            I will have multiple rows for hire & terminations for same employee. With two records, your calculation will perfectly work. I want to exactly do the same thing in excel.



            Nutan Patel

            • 3. Re: LOD calculation help
              Simon Runc

              Well as long as the MAX date for each employee is their Termination date, it should all work fine, but if there are other dates after the termination then you can ammend the formula to


              {FIXED [EmpID]: MAX(IIF([Action] = 'TER',[Effective Date],NULL))}


              this will also be better, as the original fomula I provided, would give someone a termination date, even if they were still employed! With this amendment, if they only had a HIR date, then this field would bring back NULL


              Not really used Excel for a while...but from my previous Excel life!...I'd either build a pivot table and LOOKUP the MAX date from this (Excel doesn't...unless added after Excel 2013, a MAXIFS function), or I'd create a custom function in VBA...

              • 4. Re: LOD calculation help
                Nutan Patel

                Thanks SIMON,

                There are other dates after the termination records too also this formula will give me maximum termination date for each employee but I want to set termination date with the respective hire date means if there are multiple hire dates & termination dates, first hire record should map with the first max effective date after hire date and so on. see in the attached image for employee id 1002.




                • 5. Re: LOD calculation help
                  Simon Runc

                  So you can do a similar thing for the HIR date, and then use these 2 LoD fields


                  So it would be


                  {FIXED [EmpID]: MIN(IIF([Action] = 'HIR',[Effective Date],NULL))}

                  • 6. Re: LOD calculation help
                    Nutan Patel

                    I guess you are miss leading the point. I am not looking for difference between min(hiredate) & max(termination date). I need something like if hire date is 1st January, 2016 and there are two termination dates then pick up the first termination date which appear after the hire date and second termination date will be set with second hire date.





                    • 7. Re: LOD calculation help
                      Simon Runc

                      So just so I'm clear...


                      The MIN Hire Date, will be the Hire date you want to take?


                      but then after that, you only want to take the First Termination Date after that Hire Date?


                      So in the below example, for a single employee, I've highlighted my understanding of the 2 dates we want



                      Let me know if that's correct, and pretty sure we can do this.


                      EmpIDEffective DateAction
                      • 8. Re: LOD calculation help
                        Nutan Patel

                        Hi Simon,


                        Simon Runc Thanks for your quick responses.

                        You are partially correct I believe. For you first point, you need to take the next termination date after the hire date ? Yes, I need that. But for the second point, MIN(Hire Date), will be the hire date which we need to pick is not what I am looking for. Hire date will be the same as in the data source but we need to pick up the termination date which is next to hire date.


                        Here I am attaching tableau packaged workbook file so that you can play with the data and below is the image which shows what I am looking for.


                        Hope its clear now.



                        Nutan Patel

                        • 9. Re: LOD calculation help
                          Simon Runc

                          hi Nutan,


                          Yes I think I have it!...however, I do have one more question!


                          So by changing the LoD on Termination date to MIN rather than MAX, it picks up the first and not the last termination date...so we now get the 61 figure.



                          However, as you'll see we no don't have a different Termination date for [Emp Id] = 1002 and [Dept Id] = 300. This wouldn't be an issue if we could use DeptID in the LoD....so we get a HIR date per [Emp Id]/[Dept]...however it looks like there is no TERMINATION/TRANSFER when moving from [Dept Id] = 100 and 200, but there is for 300. If you can let me know the rule around which depts trigger a termination, and thus re-hire in another department, and which ones need to combine the HIR and TER dates...I can add a formula to handle this.

                          • 10. Re: LOD calculation help
                            Simon Runc

                            To show you what I mean...I've created the following calculation


                            [Dept Id - SR]

                            IF [Dept Id]<=200 THEN 1 ELSE [Dept Id] END


                            So this groups [Dept Id] 100 and 200 into a single group (as it appears that there is no TER action, when an employee moves from 100 to 200, as we see when the move from 200 (where they get a TER and then a new HIR in Dept 300)


                            Once we have this, we can include this level in the LoD formulas...and so get multiple employment length per employee.