3 Replies Latest reply on Feb 19, 2012 9:57 PM by Shawn Wallwork

    % change between 2 quarters, and cumulative totals

    chris.wisniewski Feb 18, 2012 5:31 PM

      I'm trying to figure out a way to allow a dashboard user to select different time periods to show % change over time, while also showing cumulative total of the two  time-periods selected. Example: a user would like to see % change in their sales revenue from Q2 to Q4 in 2011 and also the cumulative total of their sales revenue from Q2 to Q4 (which would include Q3).

       

      Thanks. Im new to these forums but have been impressed with how helpful everyone is!

        • 1. Re: % change between 2 quarters, and cumulative totals
          Shawn Wallwork
          Shawn Wallwork Feb 19, 2012 6:57 AM (in response to chris.wisniewski)

          Hi Chris, the attached workbook is one way of doing what you want. I'm sure there are other ways of doing this using table calculations (which I'm still trying to get my head around). Richard will probably come back with something a lot more elegant, but in the meantime this should get you going.

           

          Chris-2.PNG

           

          One note: You have enough columns that you're going to hit up against the default setting of 6. Go here:

          Chris-3.PNG

          And then bump up the Row setting (I know, seems counter intuitive to me too):

           

          Chris-1.PNG

           

          Hope this is what you're looking for.

           

          --Shawn

          1 of 1 people found this helpful
          • 2. Re: % change between 2 quarters, and cumulative totals
            chris.wisniewski Feb 19, 2012 1:50 PM (in response to Shawn Wallwork)

            Shawn, thanks, this is helpful start.  However, I am trying to make the starting and ending quarters dynamic, based on user selection.  I've tried using parameters and filters and can't get it right.  In other words, if user selects Start Date to be Q3 2009 and End Date to be Q1 2010, then the percent change should be between sales in Q3/09 and Q1/10, while cumulative sales should be all sales in that time period. 

             

            I am trying to play with it using parameters, but can't get the column headings to display correctly.

            • 3. Re: % change between 2 quarters, and cumulative totals
              Shawn Wallwork
              Shawn Wallwork Feb 19, 2012 9:57 PM (in response to chris.wisniewski)

              Sorry Chris I miss the parameter/dashboard part. The attached workbook has the parameter controls you wanted. I was a little confused about the difference between My Sales and My Cum Sales. So I gave you a lot of options, here'a a bit of a score card:

               

              Sales:  Total of all sales by product without any date limits (notice they don't change with the parameters)

              My Sales Selected Quarters: Total sales by product within the date limits (this is probably what you call cumulative)

              My Begin Quarter Only: Just what it says

              My End Quarter Only: Just what it says

              My % Change: Is the percent change from the begin quarter selected to the end quarter selected

               

              Same for Competitor.

               

              Nothing fancy:

               

              Chris-1.PNG

               

              Hope this is closer to what you're looking for.

               

              --Shawn