7 Replies Latest reply on Jan 18, 2019 8:02 AM by S Kailay

    Creating a calculated field to identify name changes

    S Kailay

      Hi all,

       

      How do I go about creating a calculated field to help identify name changes in a table of data, using date parameters?

       

      In the very simple workbook attached, what I'm looking to achieve is a calculated field that returns a Y/N based on the following:

       

      • 2 date periods are chosen via 2 parameters
      • Only ascertain where "Name" has changed for a particular "ID No" where that "ID No" exists over both date periods e.g. if Feb-18 and Mar-18 are chosen, then the name changes in "ID No" 1 and 3 are identified (ID No 2 does not exist in the Mar-18 period)

       

      Thanks in advance!

       

      Regards,

       

      Sandeep

        • 1. Re: Creating a calculated field to identify name changes
          Ken Flerlage

          First, you'll need to create your start and end date parameters. From there, the following calculated field ought to do it for you.

           

          Name Changed

          // Count the number of different names for the ID.

          // The LOD will get a total count for only those dates within the range.

          IF {FIXED [ID no]: COUNTD(IIF([Date]>=[Start Date] AND [Date]<=[End Date],[Name], NULL))}>=2 THEN

              "Y"

          ELSE

              "N"

          END

           

          See attached.

          • 2. Re: Creating a calculated field to identify name changes
            S Kailay

            Hi Ken,

             

            Many thanks for your prompt response.

             

            Couple of quick questions:

             

            • Could you save your workbook as Tableau version 10.5.2 please?
            • Could you walk me through the logic of the calculation:

               {FIXED [ID no]: COUNTD(IIF([Date]>=[Start Date] AND [Date]<=[End Date],[Name], NULL))}>=2

             

            I understand that it only counts if it falls within the parameter dates.

             

            What does this part do? [Name], NULL))}>=2

             

            • What I need to show in my end result is as follows for only those Names that have changed:

                

                 ID No, Start Date Name, End Date Name - So that end-users can see the before and after change

                

             

             

             

             

            • 3. Re: Creating a calculated field to identify name changes
              Ken Flerlage

              OK, let's break this into its component parts.

               

              • This uses a FIXED Level-of-Detail calculation, which allows us to sort of step outside of the viz level-of-detail. For more details, see FIXED Level of Detail Expressions - Tableau
              • We're fixing on ID No, then for that ID performing COUNTD(IIF([Date]>=[Start Date] AND [Date]<=[End Date],[Name], NULL). Again, let's break this down:
                • IIF([Date]>=[Start Date] AND [Date]<=[End Date],[Name], NULL) - IIF takes three parameters: 1) Test, 2) return value if test is true, 3) return value if the test is false. So this will see if Date is in between the Start Date and End Date. If it is, then the statement will return Name. If not, it will return NULL.
                • the COUNTD around this statement will count the distinct values returned from IIF. The key to this is that COUNTD does not count NULL values. So, the result will be that it only counts distinct names where the date is between the start and end dates.
                • So, in plain English, this will tell us how many distinct names there are for each ID. The final IF XXXX >=2 will check to see if the ID has at least 2 different names. If so, that means that the person's name has changed at least once.

               

              To get the start and end date names, we'll have to create some calculated fields similar to the one I created before.

               

              Start Date Name

              // Person's name as of the start date

              {FIXED [ID no]: MAX(IIF([Date]<=[Start Date], [Name], NULL))}

               

              Last Effective Date

              // Get the last effective date for each person, but only those that fall before the end date.

              {FIXED [ID no]: MAX(IIF([Date]<=[End Date], [Date], NULL))}

               

              End Date Name

              // Get the person's name as of the last effective date.

              {FIXED [ID no]: MAX(IIF([Date]=[Last Effective Date], [Name], NULL))}

               

              The you can build the view you need as follows:

              See attached workbook in 10.5 format.

               

              If this resolves your question, please be so kind as to mark my response as the "correct answer" so we can close the thread and others can quickly find the answer to similar questions. Thanks!

              1 of 1 people found this helpful
              • 4. Re: Creating a calculated field to identify name changes
                S Kailay

                Hi Ken,

                 

                This is great! Many thanks for your detailed response.

                 

                Regards,

                 

                Sandeep

                • 6. Re: Creating a calculated field to identify name changes
                  S Kailay

                  Hi Ken,

                   

                  A very quick follow up question if I may - how do I restrict the Start and End date calculations to only apply to those ID No that are present in both periods?

                   

                  e.g. ID No 2 doesn't exist as at 31/03/18, yet if I have my Start date as 28/02/18 and End date of 31/03/18 ID No 2 still appears in the result?

                   

                  Thanks in advance,

                   

                  Sandeep

                  • 7. Re: Creating a calculated field to identify name changes
                    S Kailay

                    Hi Ken,

                     

                    Please ignore my last question - I figured it out by dropping Name Changed on Filters and setting it to "Y".

                     

                    Works perfectly :-)

                     

                    Cheers,

                     

                    Sandeep