9 Replies Latest reply on Sep 4, 2017 10:00 AM by John Powell

    Displaying contract extensions (recruitment industry)

    John Powell

      I have a report that shows all the live assignments or contractors that we have running, where they're working for us, the job title, start and end dates etc.

       

      What I need to be able to show is when we filter by end date to be able to see all those ending say this week but highlight any that have had an extension to their contract.

       

      The users are creating an event on the database Tableau is connected to that will have it's own reference but I'm trying to figure out who I can see that's the case and display it either by another row or colour it or some sort of marker.

       

      I thought an IF statement but I can't see how I'd write it.

        • 1. Re: Displaying contract extensions (recruitment industry)
          John Powell

          Trying to clarify this a bit, in my head it works like this (this is not a correctly written statement, just verbalising what it needs to do):

           

          IF end_date of booking_ref1 < end_date of booking_ref2

          THEN "Yes"

          ELSE "No"

          END

           

          If there's no further booking ref linked to the person on the row then also a no.

           

          I can't see that this can be done

          • 2. Re: Displaying contract extensions (recruitment industry)
            Jim Dehner

            John

            Gladly work on

            It can be done but I would need to see your workbook (Packaged workbooks: when, why, how ) - but it is a complex viz

             

            Jim

            • 3. Re: Displaying contract extensions (recruitment industry)
              John Powell

              Thanks Jim.  Workbook attached.

               

              There's a tab for "All Assignments (by End Date)"

               

              What I need is a marker of some sort to show that although this particular contract is coming to an end that an extension has been made.  I can of course see that if I extend the end date filter but that doesn't server the purpose of seeing who's ending this week and marking who has been extended.

              • 4. Re: Displaying contract extensions (recruitment industry)
                Jim Dehner

                Hi John

                What - very complex workbook - I am not tracking with you on how you can tell a contract has been extended - I am seeing only "End Date" as dimension and I don't see any flag that would indicate an extension or that the date has been changed -  How can you see an extension?

                 

                Jim

                • 5. Re: Displaying contract extensions (recruitment industry)
                  John Powell

                  Hi Jim

                   

                  I guess you probably can't do this but if you were to extend the date range you're looking at you'll see another row for the extension.  That itself would have an Event Ref (dimensions --> Event --> event Ref) that differs from the previous one and has it's own start and end date.  Although we don't display the event ref on the report itself as it's meaningless to anyone else by me.

                   

                  I've attached a screenshot on the assumption you probably can't extend those date ranges in the workbook.

                   

                  So we can extend the date range to see them but what we use this for is seeing which contractors are finishing up this week and who to contact about new jobs.  If we simply extend the date range the report is no longer showing us what we want to see.

                   

                  Essentially I need to be able to show all the contracts that are due to end this week but highlight the ones that then have a new event ref linked to the same company, job etc. but has a different event ref (which should be a greater number then the current one) AND where the start date is after the end date of the previous.

                  • 6. Re: Displaying contract extensions (recruitment industry)
                    Jim Dehner

                    John I think I have an approach for you but I am having difficulty with the different date formats here in the US

                    I will walk you through the approach and you can try it and see what it does

                     

                    The idea here is to use a fixed max and fixed min end date for each unique opportunit

                     

                    fixed max =          {FIXED [Consultant],[Opportunity Ref], [Candidate]:max([End Date])}         

                    (note I used the 3 dimensions only out of ignorance - if 1 will do then just use that)

                    fixed min =          { FIXED [Consultant], [Candidate]:min([End Date])}

                     

                    And the check =

                     

                              if ATTR([fixed max end date])>WINDOW_MAX(max([End Date]))

                              and attr([fixed max end date])<>ATTR([fixed min end date])

                               then "red" end

                     

                     

                     

                    things to note -

                    The Window_Max(Max([end_date]) is to get the max date from your filter

                    this is a table calculation and it must be set to DOWN

                     

                    Next you put this on color tile and then edit the color tile as shown

                     

                    Now it will color the VALUES - red when the condition of the extended date is met

                     

                    Now here is the problem I keep running into

                     

                    this is a print of part of a check screen I have been using - the column marked 1 is the Max end date for the permutation created by the formula above

                    the column marked 2 is the actual date in the sheet -  There are 2 rows with the values shown in Red indicating they meet the criteria but if you look at the 2 columns noted above you will see the calculated max date is in 2018 and in no way matches the actual date - I checked to see if there were other records for the permutation but could find none (maybe hidden in the data somewhere?

                     

                    all the other dates seem to match up OK

                     

                    That's where I am on this You should be able to add the check and max min dates and highlight the values - but I have not been able to test the data fully

                     

                    Let me know

                    Jim

                    If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

                    • 7. Re: Displaying contract extensions (recruitment industry)
                      John Powell

                      Jim that's amazing!  Thank you.

                       

                      The date might actually tie up if that's the max date for the extension.  I suspect the others tie up because they have no extension where as those 2 are showing the date of the row you can't see not having full access to our data.

                       

                      Nearly home time here in the UK so I'll give this a go tomorrow and let you know how I get on.

                       

                      Thanks again, that's a massive help!

                      1 of 1 people found this helpful
                      • 8. Re: Displaying contract extensions (recruitment industry)
                        John Powell

                        Jim Dehner

                         

                        Jim this worked really well!  Everything looks to tie up correctly.

                         

                        Thank you very much for your help.

                         

                        John

                        • 9. Re: Displaying contract extensions (recruitment industry)
                          Jim Dehner

                          Thanks John

                          I wondered if it was what you needed - your workbook is so complex it was difficult to tell

                           

                          Jim