3 Replies Latest reply on Apr 22, 2013 6:01 PM by Parker Schlank

    Simple YOY Calculation

    Parker Schlank

      Hello!

       

      Please excuse my question if it's overly simple.  I've been dragging & dropping for the better part of an hour without any progress.

       

      I'm trying to add two simple calculations to my Sheet 1.  I would like them to appear where the two black boxes are as #1 & #2 respectively.

      Untitled.png

      #1 - a column that calculates the difference between [1Q2012;Total] & [1Q2013;Total] as an integer.  (The answer in this example case would be 17,813)

      #2 - a second column that calculates the difference between [1Q2012;Total] & [1Q2013;Total] as an percentage.  (The answer in this example case would be +16.7%)

       

      I would like them to be dynamic, in that if I were to drop something onto the row filter (say client name) that newly added columns #1 & #2 would update accordingly.

       

      If anyone can aid in how I might go about setting this up, I would be very appreciative!

       

      Thanks in advance!

       

      - Parker

        • 1. Re: Simple YOY Calculation
          Tracy Rodgers

          Hi Parker,

           

          These values can be found. However, they won't be able to be placed in the desired place on a worksheet. Each value can be found on separate sheets and then placed on a dashboard together in the desired view.

           

          To get #1, create a calculated field similar to the following:

           

          (ZN(SUM([TrxCount])) - LOOKUP(ZN(SUM([TrxCount])), -1))

           

          To get #2, the calculation should look like the following:

           

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

           

          Hope this helps!

           

          -Tracy

          1 of 1 people found this helpful
          • 2. Re: Simple YOY Calculation
            Parker Schlank

            [Tracy]

             

            This calculation is very helpful.  Thanks for passing along.

             

            I added the calculation and changed [Calculation1] to [Compute Using: Advanced], [Addressing: Year of Date].  This gave me YOY growth numbers I was trying to compute.

             

            Untitled2.png

            I then added [Calculation1] to the Measure Values, but of course I wanted to see it as a 'column' not a row:

             

            Untitled3.png

             

            So I then grabbed on the row box icon:

            Untitled6.png

            and dragged the icon up to the column:

             

            Untitled4.png

             

            This gave me nearly the result I was looking for, with the only exception being that 2012 shows up on the display & is 'blank'

             

            Untitled5.png

             

            Would it be possible to write additional syntax in the formula to isolate / hide the blank 2012 values (above in red)?  If so, I would call this close enough for the report I'm trying to produce.

             

            Thanks again for any insight!

             

            - Parker

            • 3. Re: Simple YOY Calculation
              Parker Schlank

              [All]

               

              Figured it out.  Needed multiple calculated fields:

               

              [1Q12] = IIF(YEAR([Date]) = 2012, IIF(MONTH([Date]) < 4, [TrxCount], 0), 0)

              [1Q13] = IIF(YEAR([Date]) = 2013, IIF(MONTH([Date]) < 4, [TrxCount], 0), 0)

              [B(W) #] = SUM([1Q13]) - SUM([1Q12])

              [B(W) %] = IIF(SUM([1Q12]) = 0, 1, (SUM([1Q13]) - SUM([1Q12]))/SUM([1Q12]))

               

              Piece of cake actually.

               

              Example workbook attached.  Hopefully this helps someone else that was looking for this information!

               

              - Parker