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

    Simple YOY Calculation

    Parker Schlank



      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.


      #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!



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



            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.



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




            So I then grabbed on the row box icon:


            and dragged the icon up to the column:




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




            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



              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