5 Replies Latest reply on Mar 3, 2016 5:30 AM by pooja.gandhi

    Text Compare Across Columns - Help!

    Erik Wempa

      I'm looking to find a way to flag differences in text field values between columns.  I've attached an example.

       

      In my example, I want to compare the values between columns:  CLUB NAME (2013), CLUB NAME (2014) and CLUB NAME (2015).

       

      Records 1-3 can remain as is, as the club name values remain consistent across all columns.

       

      For record 4, however, I want to flag the values in CLUB NAME (2014) and CLUB NAME (2015) since the value changes from "North Star Bar" to "Milkboy."  Ideally, I'd like to color-code these two values in red, so that a user could see the values changed from one column to the next.

       

      Records 5, 6, 8-10 all remain as-is, as the club name values remain consistent across all columns.

       

      For record 7, however, I want to flag the values in CLUB NAME (2013) and CLUB NAME (2014), since the value changes from "Rams Head Live" to "Baltimore Soundstage."  Again, a color-coding would be great, so that the user can see that the values changed from one column to the next.

       

      Any ideas would be appreciated.

        • 1. Re: Text Compare Across Columns - Help!
          Steve Mayer

          In the attached workbook, I created a calculated field that returns True if there are any changes in your venue names between 2013, 2014 and 2015, and then used a simple colored shape to highlight the ones that changed (color is white on the others, so they appear invisible).

           

          Formula for the calculated field:

           

          ATTR([Club Name (2013)]) <> ATTR([2014 (VENUE_ID)].[Club Name (2014)])

          OR

          ATTR([Club Name (2013)]) <> ATTR([2015 (VENUE_ID)].[Club Name (2015)])

          OR

          ATTR([2014 (VENUE_ID)].[Club Name (2014)]) <> ATTR([2015 (VENUE_ID)].[Club Name (2015)])

           

          -Steve

          • 2. Re: Text Compare Across Columns - Help!
            pooja.gandhi

            You can also do something like this, if you actually need the text to be colored.

             

            I placed MIN(Number of Records) thrice on columns so that it creates a separate axis for each measure. Changed the mark type to 'text' for all axis and fixed the axis 0-2 so the text is centered. Placed each measure on its corresponding axes. Then I just created 2 calculated boolean fields:

             

            2013 = 2014: ATTR([Club Name (2013)]) = ATTR([2014 (VENUE_ID)].[Club Name (2014)])

            2014 = 2015: ATTR([2014 (VENUE_ID)].[Club Name (2014)]) = ATTR([2015 (VENUE_ID)].[Club Name (2015)])

             

            Placed the 1st calculated boolean on color on the marks card for the first axis and 2nd calculated boolean on color on the marks card for 2nd and 3rd axes.

             

            PS: Baltimore Soundstage for venue 7 is spelled wrong in your 2015 venue_id datasource and hence it colors it. If you fix it, this should work correctly.

             

            • 3. Re: Text Compare Across Columns - Help!
              Erik Wempa

              Thank ya kindly!  I'll need to see if my user agrees to this approach, but I think it might serve her needs.

               

              Much love and respect, yo!

              • 4. Re: Text Compare Across Columns - Help!
                Erik Wempa

                Thank ya kindly!  I like this approach, but I'm not sure if my user will like seeing the MIN NUMBER OF RECORDS on the sheet.  Still, it's a worthwhile option to consider, and I thank you for your time.

                 

                Much love and respect, yo!

                • 5. Re: Text Compare Across Columns - Help!
                  pooja.gandhi

                  You can right click on the axes and uncheck 'show header'. It looks pretty clean if you do that: