4 Replies Latest reply on Jun 13, 2018 11:06 AM by michael.zehrer.0

    Compare data in one column

    michael.zehrer.0

      I'm trying to create a dashboard that has two maps with one showing 2017 and one showing 2018. Depending on the "Company" selected in a filter it will show the footprint for 2017 in one map and footprint for 2018 in another map (I have this part done). I want to compare 2017 geographies to 2018 geographies and color the ones that appear in both "blue" and the ones that are only in 2017 or only in 2018 "red". It seems like it should be very simple but I'm struggling.

       

      I have data that looks like:

      CompanyYear
      State
      Starbucks2017Colorado
      Starbucks2017Nevada
      Mcdonalds2017Colorado
      Mcdonalds2017California
      Starbucks2018Colorado
      Starbucks2018California
      Mcdonalds2018Texas
      Mcdonalds2018California
      Mcdonalds2018Arizona

       

      So the maps for each would have:

           Mcdonalds 2017 would have California "Blue" and Colorado "Red"

           Mcdonalds 2018 would have California "Blue" and Arizona/Texas "Red"

       

           Starbucks 2017 would have Colorado "Blue" and Nevada "Red"

           Starbucks 2018 would have Colorado "Blue" and California "Red"

       

      I've tried a parameter and a calculated field, but I can't seem to get it to work. How can I compare data within a column?

       

      Thank you in advance.

        • 1. Re: Compare data in one column
          Zhouyi Zhang

          Hi, Michael

           

          Please find my solution attached.

           

          Below is the sample result

           

          Hope this helps

           

          ZZ

          • 2. Re: Compare data in one column
            michael.zehrer.0

            Hey ZZ,

             

            This is extremely helpful and did exactly what I asked for. However, I realize now I made my example a little too simple. I actually have multiple years, so your approach is causing issues in situations like:

             

             

            CompanyYearState
            Starbucks2017Colorado
            Starbucks2017Nevada
            Mcdonalds2017Colorado
            Mcdonalds2017California
            Starbucks2018Colorado
            Starbucks2018California
            Mcdonalds2018Texas
            Mcdonalds2018California
            Mcdonalds2018Arizona
            Mcdonalds2016Colorado
            Mcdonalds2016Arizona
            Mcdonalds2016Nevada

            So in this example, I want the map colored exactly as it was in your first iteration, but it's giving me a count of 2 for "Colorado" and "Arizona" because they also appear in 2016. However, 2016 shouldn't impact the color because I only want to compare 2017 v 2018. I thought this could be done with a level of detail, but I can't figure out how to get it to let me say "only 2017 v 2018"

             

            Thank you much!

            • 3. Re: Compare data in one column
              Zhouyi Zhang

              Hi, Michael

               

              Please find my updates attached.

               

               

              Hope this helps

               

              ZZ

              • 4. Re: Compare data in one column
                michael.zehrer.0

                ZZ,

                 

                Thanks a lot. You got me where I was wanting to go. Now I tried changing it slightly and adding another layer of complexity and am having issues. I decided to pivot to a single map and just show any "new" geographies from the prior year. I want user to be able to select a single year and see what was the same from last year in blue and what's new in red. Your code works perfectly for one year, but if I go back multiple years I start having issues. I solved it with a parameter control for the calculation, but it requires me to enter information twice. If you have a chance, I'd love for you to take a peak at the new problem! Re: Calculations with parameter/quick filter

                 

                Thanks again for your help.

                -MZ