4 Replies Latest reply on Jul 28, 2016 10:24 AM by Stoyko Kostov

    % Change between two columns

    Lindsay O'Keefe

      Hi,

       

      I would like to show the percentage change between the FY16 and the FY15 MQL and SQL Columns on the "YoY" tab on the attached workbook. I would like it to be a separate column after FY16.

       

      Is this possible?

        • 1. Re: % Change between two columns
          Stoyko Kostov

          Hi Lindsay,

           

          The easiest thing to get the YOY percent change is the following:

          1. Duplicate the MQL measure

          2. Drag and drop the copy you just created to the Measure Values mark, just after the MQL

          3. Right-click on the copy you just dropped and choose Quick Table Calculation - Percent Difference.

          4. You get what you need - edit its alias to name it as you wish, e.g. MQL YOY %.

           

          This has one small caveat: you get a column for the first year in your table - FY15 - as there is nothing to compare it against. If this doesn't bother you - just repeat the above steps for the SQL measure and you are done.

           

          If it bothers you, I can only propose a hacky solution. You can't just add a filter on FY15 in MQL YOY %, as it will apply to the entire sheet and will hide other values that you want to see.

           

          The hack that I propose is to create the MQL, MQL YOY%, SQL, and SQL YOY% in different worksheets, and then combine them in a dashboard in the order you need.

           

          That way you can add filters separately on each worksheet, without affecting the other worksheets.

           

          To create the MQL YOY% sheet, add a calculated field DiffNon0 with the following definition:

          IF FIRST() <> 0 THEN

          (ZN(SUM([MQL])) - LOOKUP(ZN(SUM([MQL])), -1)) / ABS(LOOKUP(ZN(SUM([MQL])), -1))

          ELSE 0

          END

          Make sure it is computed along Table(Across).

          Then drag DiffNon0 to Text and to Filter, and exclude the 0 values. That will remove the first column.

           

          I'm attaching the workbook I prepared: from your workbook I only kept the YoY sheet, and then created YoY Table Calc Easy with the first solution I propose above, and sheets MQL and MQL YOY% and a dashboard with the second solution I proposed.

           

          Hope this helps - don't hesitate to ask if you have more questions!

          2 of 2 people found this helpful
          • 2. Re: % Change between two columns
            Lindsay O'Keefe

            Thank you! This is helpful!

            • 3. Re: % Change between two columns
              Kyle Boyce

              Could you guys help me? I'm not understanding the fix. I've opened the workbook that you have attached as a fix but the MQL calc field is just showing as 1. Here is a sample of the data that i made. I need YoY% difference on a separate sheet so that I can **** it up next to my YoY numbers.

              • 4. Re: % Change between two columns
                Stoyko Kostov

                Responded to Kyle in the thread he started - Re: Help! - Displaying Year Over Year (YoY) .

                1 of 1 people found this helpful