4 Replies Latest reply on Jun 23, 2012 7:35 PM by Joe Mako

    Calculated ratio

    Michael Nealey

      Hi -

      I'd like to create a ratio that takes my measure (say, profit) and subtracts May 2011 profit from May 2012 profit, and does the same for every month of data I have...(i.e. June 2011 from June 2012 ad infinitum). I'd plot this ratio on a line showing trend over time.

      I'm confused as to how I would go about doing this?

        • 1. Re: Calculated ratio
          Tracy Rodgers

          Hi Michael,


          One way to do this is to create a table calculation similar to the following:


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


          Then, depending how the view is set up, the calculation will need to change how it is being computed. So if Year(Order Date) and Month(Order Date) were both on the rows shelf and this calculation was on the text shelf, right click and select Edit Table Calculation. Compute Using-->Advanced. Add Month of Order Date and Year of Order Date to the Compute Using box. Then, place Year of Order Date at the level and Month of Order Date for Restarting every.


          Hope this helps a bit!



          1 of 1 people found this helpful
          • 2. Re: Calculated ratio
            Michael Nealey

            Hi Tracy -

            Thanks for your answer...the calculation seems to work but if I wanted to show this with a continuous line, it seems more difficult and the calculation fails...(since I can't show both the quarter and year pills at the same time)...


            • 3. Re: Calculated ratio
              Michael Nealey

              To clarify my response, I cannot seem to get the correct calculation in a continuous date; I can make it work with a discrete date (blue pill), but this does not give me the view I am looking for.

              Is there a different calculation I need to create for a continuous line?


              • 4. Re: Calculated ratio
                Joe Mako

                Can you provide a sample packaged workbook that represents your situation?