2 Replies Latest reply on Aug 14, 2012 2:08 PM by Mark Holtz

    Calculating year over year growth based on todays date compared to this time last year

      I have historical sales data for the last 5 years and I want to measure the growth rate from year to year.  The problem is that i do not want to compare to this years partial data (August so 8 months) to last years full 12 months of data.  Right now I am looking at sales up until "today" in 2012 to the full year 2011.  I want to compare "today" the first 8 months of 2012 data to the first 8 months of 2011 data, and the first 8 months of 2010 data and so.  Is there a way to set this up so I can compare these figures more appropriately.  and if possible can be adjustable so if I choose like June 15, it compares data in each up up until June 15th, 2012, June 15th 2011, June 15th 2010 or I could pick Agust 14 and it show up until August in 2012, 2011, 2010...

       

      Thank you for any help you can lend me

       

      Thanks,

       

      Nate

        • 1. Re: Calculating year over year growth based on todays date compared to this time last year
          Jeff Rice

          A possible way to solve this would be to put a Date Field on the Filter shelf that sets all dates to a similar year.  You can do this using a Calculated Field similar to the one below. 

           

          1. This Calculated Field parses out the different date parts of the date field into strings, adds them together with the appropriate dash marks, then adds an arbitrary year to the end, and finally wraps the whole thing in a Date function.

          pic1.jpg

           

          2.      Once you do this, you can put the newly created calculated field on the filter shelf as ‘Range of Dates’ filter. 

          3.      You can then make it a Quick Filter.  Before doing this you may want to format the date field to only show the month and day. 

          pic2.png

           

           

          4.  You will need to put your original date field on the columns shelf (in this case I put the Month of Order Date  from Superstore. Put the Year of Order Date on the Color shelf.  You can now filter from any Month/Day of different years and compare them appropriately. 

          pic3.png

          5.  You can add additional functionality if  you like by adding a Quick Table Calculation of Year over Year Growth and adding it on a dual axis.

          pic4.jpg

           

          Hope this helps. 

          • 2. Re: Calculating year over year growth based on todays date compared to this time last year
            Mark Holtz

            I believe you could accomplish this with a Date Parameter and then a couple of calculated fields.

            The Data Parameter is simple--just call it something like "ChooseDate" and use it in conjunction with calculated fields.

             

            [Last Year] (2011) would be:

            DATE(

            STR(MONTH([ChooseDate])) +'/'

            +STR(DAY([ChooseDate])) +'/'

            +STR(YEAR([ChooseDate])- 1 ) //change

            )

             

            [2 Years ago] (2010) would be:

            DATE(

            STR(MONTH([ChooseDate])) +'/'

            +STR(DAY([ChooseDate])) +'/'

            +STR(YEAR([ChooseDate])- 2 )

            )

             

            So you could do make a calculated field called "2010 Sales" as:

            IF YEAR([SaleDate]) = 2010 AND [SaleDate] < [2 Years Ago]

            THEN [Amount]

            ELSE 0

            END

             

            2011 would be:

            IF YEAR([SaleDate]) = 2011 AND [SaleDate] < [Last Year]

            THEN [Amount]

            ELSE 0

            END

             

            Then you play with those variables.  If you still need help, could you post your workbook?

            1 of 1 people found this helpful