2 Replies Latest reply on Feb 28, 2017 10:10 AM by Navya Thumula

    Period over period Calculations

    Navya Thumula



      I have been trying to calculate period over period calculations. Requirement is something like this- I have a time selector filter Recent 4 week, recent 13 week etc.

      If I select time selector as R4 week, I should get a view that shows the vale for recent 4 week period and the previous 4 week period. If I select time selector as R13 week, I should be able to compare values of current 13 weeks and previous 13 weeks.


      And also how to implenet a filter that selects R4 week and R13 week.


      Thanks & Regards


        • 1. Re: Period over period Calculations
          Jim Dehner


          See the attached t10.1 workbook


          The approach is to create 2 parameters -

          1. Start Date - use a  date - Weeks format
          2. Time Bucket - use and Integer format - the values are -4 and -13

          Then you create 2 Calculated fields

          1. Current period >>IF ([Ship Date])>(DATEADD('week',[Time bucket in weeks],[start date (week)])) and [Ship Date]<= [start date (week)]  then [Sales] END
          2. Past Period >>IF ([Ship Date])>(DATEADD('week',(2*[Time bucket in weeks]),[start date (week)])) and [Ship Date]<= (DATEADD('week',([Time bucket in weeks]),[start date (week)])) then [Sales] END



          Let me know how that worked



          2 of 2 people found this helpful
          • 2. Re: Period over period Calculations
            Navya Thumula

            Hello Jim,


            Thank you for the reply. I did the same thing and it works. But I had had one more interactivity to select week or month. So I have followed below steps:

            1. created parameters- Period type (Week, Month) and length

            2. Use a custom start date- I wanted this to be the max date in the data.

            3. Created a calculated field to see the rolling period and previous period values:

            IF DATEDIFF([Period Type],[Date],[Custom Date]) >=0 AND DATEDIFF([Period Type],[Date],[Custom Date])< [Period Length] THEN 0

                    ELSEIF DATEDIFF([Period Type],[Date],[Custom Date]) >= [Period Length] AND DATEDIFF([Period Type],[Date],[Custom Date]) <2*[Period Length] THEN -1



            Thank you!