4 Replies Latest reply on Aug 28, 2018 10:00 AM by vengadesh palani

    Current vs Previous N Period to Date AVG Comparison

    vengadesh palani

      Hi All

       

      i want to compare the current Period value with Previous N Periods Avg Value  based on period selection (YTD, QTD, MTD, WTD)

      1.png

       

      Ex:

      Period = Month

      Previous N Periods = 2

      Date Selection = 12/02/2015

       

      so Current Month to Date value is 8

       

      DateValue
      12/01/20155
      12/02/20153

       

      Previous 2 Periods Avg --> (10+8)/2 --> 9

       

      DateValue
      11/01/20154
      11/02/20156
      10/01/20153
      10/02/20155

       

      Reference URL : Current vs Previous Period to Date Comparison

      this reference does not have Previous N Periods Avg value

       

      How can i achieve this Logic?

       

      Thanks

        • 1. Re: Current vs Previous N Period to Date AVG Comparison
          Joe Oppelt

          What you are asking for is going to take a lot of steps.  Let's start with the first step first:

           

          I do stuff like this by creating a [Begin Period] calc and an [End Period] calc.

           

          In your example, the values you would set for the two calcs would be 12/1/2015 and 12/2/2015 respectively.  You would get that by setting the [End Period] calc to the parameter setting you have for Date Selection.

           

          Next you would set the [Begin Period] calc by doing a DATETRUNC on the parameter value.


          Now here's the thing.  Change your parameter for [Period] so that the internal values are all lowercase.  You can leave the displayed values uppercased as you already have them, but you want the internal value to be lowercase.  The reason is this:  The DATETRUNC takes two arguments:  'date part', and [Your Date].

           

          (You can find date functions here:  Date Functions  )

           

          The date part uses a lower case token for things like 'quarter', 'month', etc.  If your param already has them lowercased for the internal value, you can just use your param for that argument:

           

          DATETRUNC([Period], [Date Selection])

           

          If the user selected Month for the Period, Datetrunc would return a value of the first of the month in [Date Selection].  If the user selected Quarter, then Datetrunc would give you the first day of the quarter in which [Date Selection] falls.


          See if you can get that to work first.

           

          (PS:  A sample workbook with sample data would help me show you things.  Can you upload a workbook and data set we can use here?)

          • 2. Re: Current vs Previous N Period to Date AVG Comparison
            vengadesh palani

            Thanks for your response

             

            Exactly i need a result like below image its working only for Month selection

            please find attached Excel and Workbook in the main thread

             

            result.png

            Flag.png

            • 3. Re: Current vs Previous N Period to Date AVG Comparison
              Joe Oppelt

              See attached.

               

              I made the [Start Date] and [End Date] calcs I suggested above.

               

              Then I made a calc that tells me the beginning period date for every row.  So if the date value in a row is May 10, and the user selects "month", the beginning period date for May 10 is May 1.  For "quarter" it's April 1.  For "year" it's Jan 1.  Etc.  You can see that on SHeet 8.

               

              I also created a calc called Days to Grab.  If the user selects 11/2/2015 and MONTH, the days to grab is 2.  If QUARTER it's 33.  Etc.

               

              Then I made a calc called [Get this date?].  It uses [Begin Date for this Period] and [Days to Grab] and Start Date and End Date to determine if the given row should be grabbed.  All these are on Sheet 8.  Notice that I don't have any filters on this.

               

              But then you can use [Get this date] as a filter, and you'll only get the rows you need to get.

               

              On sheet 9 I showed a way you can use these calcs.

              • 4. Re: Current vs Previous N Period to Date AVG Comparison
                vengadesh palani

                Thanks Joe. It's Working fine.