3 Replies Latest reply on Nov 24, 2018 7:50 AM by Frederique Bordignon

    Comparing multiple-values dimensions between columns

    Frederique Bordignon

      Hello,

       

      I have the follwing data:

      Capture d'écran 2018-11-23 16.13.58.png

      I would like to create another table where each cell mention whether there is a change or not comparing to the year before. Something like this:

      Capture d'écran 2018-11-23 16.25.00.png

       

      I would appreciate any kind of help..

       

      Thanks a lot.

       

      f

        • 1. Re: Comparing multiple-values dimensions between columns
          Norbert Maijoor

          Hi Frederique,

           

          lookup.png

           

          1. M1. lookup Id: LOOKUP(attr([Id]),-1)

           

          2. M2. lookup Dom:  LOOKUP(attr([Dom]),-1)

           

          3. M3. Change/No Change:

          if FIrst()=0 then 'No Change'

          elseif attr([Id])<>[M1. lookup Id] then 'No change'

          elseif attr([Id])=[M1. lookup Id] and attr([Dom])=([M2. lookup Dom]) then 'No change' else 'Change' END

           

          Hope it helps.

           

          Regards,

          Norbert

          • 2. Re: Comparing multiple-values dimensions between columns
            Norbert Maijoor

            Once again it's proofs when you are able to take a walk & "re-think" an issue;) Find my updated approach as reference below and stored in attached workbook version 10.2 located in the original thread.

             

             

            1. M1. Dom Converted:

            if [Dom]='Psychology' then 1

            elseif [Dom]='Medicine' then 2

            elseif [Dom]='Business, Management and Accounting' then 3

            END

             

            2. M2. Dom converted diff: ZN(SUM([M1. Dom Converted])) - LOOKUP(ZN(SUM([M1. Dom Converted])), -1)

             

            3. M3. Change/No Change: if [M2. Dom converted diff]<>0 then 'Change' else 'No Change' END

             

            4. Drag the required objects to the indicated locations

            position.png

             

            Hope it helps,

             

            Regards,

            Norbert

            • 3. Re: Comparing multiple-values dimensions between columns
              Frederique Bordignon

              Thanks Norbert for your both answers, it taught me new things but I think neither of them can deal with the case when an ID has several domains (dom) and all of them are stable over time.

              That's my fault, I focused on calculating changes and my fake data were lacking variety...

              In the second version of the workbook I join, I created this example: an ID with the same 2 domains every year. The calculated field should indicate "no change".

               

              What's more, I think the second option you proposed might be difficult to implement because I have 300+ different domains.

               

              I hope you will be able to re-re-think the issue.

               

              Thanks a lot

               

              f