5 Replies Latest reply on Jul 18, 2013 12:37 PM by Kristy McGee

    Calculate from different previous period

    Benoit Pigeon


      I would like to create a sheet displaying the information below (everytime data must be compared to last year but last year should not appear on the sheet) :

      - last sales month of data

      - last three month of data (agregated) compared to the same period last year

      - for year to date


      I have tried a lot of time with different stuff like index, lookup(attr([Year]),0) etc... but I always have trouble.

      See attached a dummy workbook with raw data.

      Thanks for your help


        • 1. Re: Calculate from different previous period
          Joshua Milligan



          If I understand what you are trying to do, I think there are a couple of options.


          1. You could manually hide the year you don't want to show. (right click the header for 2007 and select "Hide").

          2. Create a table calc filter that will allow the table calculations to be computed prior to that filtering. 


          The benefit of #1 is that it is easy, but it does have some drawbacks.  One thing I don't like about "Hide" is that there is no visual cue that something is hidden and it is not dynamic.  If you do it for 2007 then 2007 will be the value that is always hidden until you change it.


          I would prefer Option 2.  I've attached your workbook with both options and also a calculated field, which is a table calc for looking up the previous value at the Year level (so it will lookup the previous value for the previous year at whatever level you are aggregating -- quarter, month, etc...).


          I'd be happy to answer any questions you might have!




          1 of 1 people found this helpful
          • 2. Re: Calculate from different previous period
            Benoit Pigeon

            Hi Joshua,

            Thanks for your help.

            I have few more question. How could I dynamicaly display only last month of data (in our case november 2008). When I had a filter the lookup doesn't work anymore



            • 3. Re: Calculate from different previous period
              Tracy Rodgers

              Hi Benoit,


              Create another calculated field similar to the following:


              if last()=0 then 'show' else 'hide' end


              Place this on the columns shelf next to MONTH(Dates). Right click on one of the column headers that say 'hide', right click and select Hide. Then, right click on the calculation and un-check the Show Header option.


              As your data updates, the latest month will show.


              Hope this helps!



              • 4. Re: Calculate from different previous period
                Benoit Pigeon

                Hi Tracy

                It works exacly as espected.



                • 5. Re: Calculate from different previous period
                  Kristy McGee

                  This is great, but I want the last month that someone selects on the quickfilter (whichever month and year they choose) to show along with the previous month in order to get the % difference between the both.  I have altered the last calculation to be last<=0, but when a user selects a month, only that month shows.  I want both to show.  Any ideas?