3 Replies Latest reply on Aug 19, 2018 9:50 AM by Okechukwu Ossai

    comparing string dimensions column A from two different rows based upon dimension in column b

    LISA MOORE

      So grateful for this forum and I'm new to data analytics and new to Tableau in the last 3 months,  Any help is appreciated. 

       

      I have a data source with Name, Date, Eye Color.  If the eye color of the Name changed to any of the from/to combinations listed below, the created calculation column "Eye color change Alert" should result in 1 in the row of the most recent date.  Always compare the two most recent dates from the date column.  Always comparing the eye color of the same person name.  Sample data results are below.  My actual data is thousands of rows long and is proprietary to my company. 

      from Blue to Red

      from Green to blue

      from Brown to green

       

      I cannot Pivot the data. The data source is not creating new pivot columns,  It is augmenting the existing columns that I'm trying to pivot, which is negatively affecting other existing data sheets using those columns. 

       

      Name

      Date

      Eye color

      Eye Color Change Alert

      lisa

      8/11

      blue

      0

      John

      8/11

      red

      0

      katie

      8/11

      Green

      0

      Mary

      8/11

      brown

      0

      lisa

      8/16

      red

      1

      John

      8/16

      Red

      1

      katie

      8/16

      blue

      1

      mary

      8/16

      Green

      1

      lisa

      8/5

      blue

      0

      John

      8/5

      red

      0

      katie

      8/5

      Green

      0

      Mary

      8/5

      brown

      0

        • 1. Re: comparing string dimensions column A from two different rows based upon dimension in column b
          Okechukwu Ossai

          Hi Lisa,

           

          The formula below could do it for you. See attached workbook.

           

          [Eye Color Change Alert]

          IF LOOKUP(ATTR([Eye color]), -1) = 'Blue' AND LOOKUP(ATTR([Eye color]), 0) = 'Red' THEN 1

          ELSEIF LOOKUP(ATTR([Eye color]), -1) = 'Green' AND LOOKUP(ATTR([Eye color]), 0) = 'Blue' THEN 1

          ELSEIF LOOKUP(ATTR([Eye color]), -1) = 'Brown' AND LOOKUP(ATTR([Eye color]), 0) = 'Green' THEN 1

          ELSE 0 END

          This is a table calculation. You need to tell Tableau the direction along which the calculation is to be performed. So, right click on the field name in the view and select 'Edit Table Calculation'. Set it up as shown in the screenshot below.

           

           

          If you want the view to be sorted by date. Make a copy of the date field and add to the view. Right click on the field name and uncheck 'Show Header'

           

           

          Hope this helps.

          Ossai

          • 2. Re: comparing string dimensions column A from two different rows based upon dimension in column b
            LISA MOORE

            Good morning Okechukwu - Thank you for the thorough response.  I ran the calculation and receive no results, which matches the manual work I did with same data in Excel. One follow-up question:

             

            My actual data is thousands of lines long and is updated weekly. I need to only compare the change in eye color from the two most recent weeks. 

             

            Does the formula above account for the restriction to only compare the two most recent weeks of data?

            • 3. Re: comparing string dimensions column A from two different rows based upon dimension in column b
              Okechukwu Ossai

              Hi Lisa,

               

              The calculation is not showing any results probably due to the inconsistency in the Excel dataset. A consistent upper and lower case pattern is required for the solution to work. For example, mary and Mary are not the same name. Red and red are not the same color also. I manually edited the data to make the capitalization consistent.

               

              I've revised the solution to only compare the two most recent weeks of data. Please clean up your dataset before running the analysis or modify the formula to match the capitalization in your database.

               

              Step 1: Create calculated field [Most Recent Eye Color]

              {FIXED  [Name]: MIN(IF {FIXED [Name]: MAX([Date])} = [Date] THEN [Eye color] END)}

               

              Step 2: Create calculated field [2nd Most Recent Eye Color]

              {FIXED  [Name]: MIN(IF {FIXED [Name]: MAX(IF [Date] <> {FIXED [Name]: MAX([Date])} THEN [Date] END)} = [Date] THEN [Eye color] END)}

               

              Step 3: Create calculated field [Did Eye Color Change?]

              IF [2nd Most Recent Eye Color] + "-" + [Most Recent Eye Color] = 'Blue-Red' THEN 'Yes'

              ELSEIF [2nd Most Recent Eye Color] + "-" + [Most Recent Eye Color] = 'Green-Blue' THEN 'Yes'

              ELSEIF [2nd Most Recent Eye Color] + "-" + [Most Recent Eye Color] = 'Brown-Green' THEN 'Yes'

              ELSE 'No' END

               

              Step 4: Create calculated field [Eye Color Change Alert]

              IF [Did Eye Color Change?] = 'Yes' AND {FIXED [Name]: MAX([Date])} = [Date] THEN 1 ELSE 0 END

               

              Hope this helps.

              Ossai