4 Replies Latest reply: Feb 12, 2013 2:58 PM by Richard Jones RSS

    Reporting over multiple rolling 12 month periods

      Hello

       

      I have a requirement to create an expression to report over multiple rolling 12 month periods based upon a month end date selected by the user.

       

      Two example scenarios below.

       

      1) User selects month end 31/03/2012.

       

      My table should display:

       

      YearCalculation
      2012Rolling 12 month sum from 01/04/2011 to 31/03/2012
      2011Rolling 12 month sum from 01/04/2010 to 31/03/2011
      2010Rolling 12 month sum from 01/04/2009 to 31/03/2010

       

      2) User selects month end  31/12/2011

       

      My table should display:

       

      YearCalculation
      2011Rolling 12 month sum from 01/01/2011 to 31/12/2011
      2010Rolling 12 month sum from 01/01/2010 to 31/12/2010
      2009Rolling 12 month sum from 01/12/2009 to 31/12/2009

       

       

      So, depending on the month end selected by the user, the table should display the rolling 12 month sum of my measure, along with the same calculation for the same period in the preceeding two years.

       

      Is this possible with set analysis?

       

      Thanks in advance for any assistance.

        • Re: Reporting over multiple rolling 12 month periods
          Alessandro Saccone

          It's possible to do this by set analysis but it is easier, when you load data, to compute foe each month end other columns related to the rolleng period you desire for instance column1: start of the first rolling period, column2: end of the first rolling period.

          In this way you can easily use set analysis simply comparing date

           

          Hope it helps

            • Re: Reporting over multiple rolling 12 month periods

              Hi Alexandros.  Thanks, this was very helpful.  I have found some good examples using a AsOf table to achieve similar results, but i prefer the idea of using two columns to bound the start and the end of the rolling periods I need.  I am working with three year rolling periods, and to generate all the date combinations for these sounds onerous.

               

              Please could you elaborate a little on how your approach would work?

               

              Thanks

            • Re: Reporting over multiple rolling 12 month periods
              Gysbert Wassenaar

              Yes, that's possible with three expressions. Something like:

               

              sum({<[MyDate]={">$(=addyears([MyDate],-1))<=$(=only([MyDate]))"}>}Sales)

              sum({<[MyDate]={">$(=addyears([MyDate],-2))<=$(=addyears([MyDate],-1))"}>}Sales)

              sum({<[MyDate]={">$(=addyears([MyDate],-3))<=$(=addyears([MyDate],-2))"}>}Sales)

               

              Add those to a straight table, don't add any dimensions and check the option Horizontal on the Presentation tab.

                • Re: Reporting over multiple rolling 12 month periods

                  Thanks for the suggestion Gysbert.  I did manage to get this working and it worked well, but I think i may need to go down the pre-computed in the script route.  The reason being, that really i need to combine the three seperate expressions into a single one; I have more expressions to build into the same table table which complicate things a little, especially when the table is made horizontal.