10 Replies Latest reply on Feb 24, 2012 7:19 AM by Joshua Kitz

    Grouping Custom Date Periods

    Christopher Henderson

      Hi

       

      I want to group the values associated to a custom date period. Currently I have sales data by month, what I want to do is to display the total sales by annual total. So as an example the annual total of October 2011 would be the sum of November 2010 sales through to October 2011 to account for a full year.

       

      I would also like to compare this to the same annual total of the previous year, for example:

       

      Oct 2010 annual total = X (sum of Nov 09 - Oct 10)

      Oct 2011 annual total = X (sum of Nov 10 - Oct 11)

       

      The database for this report is updated each month, so it would be great if the report updated with the latest months addition.

       

      Hope this makes sense,

       

      Many thanks

       

      Chris

        • 1. Re: Grouping Custom Date Periods
          Andy Cotgreave

          Hi Christopher

          Here's one solution using a couple of table calculations. The first does a sum of the current month and the previous 11:

          Window_SUM(SUM([Sales]),-11,0)

           

          The second does a lookup for the same value 12 months ago:

          LOOKUP([Sales for last 12 months],-12)

           

          And then a third calculation simply compares the two. On the workings table, everything is set up to compute using Order Date. THe chart shows the same info in a visual way.

           

          Is that what you were looking for?

           

          Be aware that this uses offsets along the table, so there needs to be a record for every month in your sales data.

           

          Andy

          • 2. Re: Grouping Custom Date Periods
            Christopher Henderson

            Many thanks Andy

             

            Is there a way to create this calculation without window sum, as I do not wish to display all of the months data, only say Oct 10 (annual total) vs Oct 11 annual total. I was thinking maybe it would be a calculated field for 2011 and one for 2010?

             

            So in a table there would just be:

             

            Oct 10 Annual Total    X

            Oct 11 Annual Total    X

            Variance 10 -11          X

             

            Thanks

             

            Chris

            • 3. Re: Grouping Custom Date Periods
              Christopher Henderson

              Many thanks Andy

               

              Is there a way to create this calculation without window sum, as I do not wish to display all of the months data, only say Oct 10 (annual total) vs Oct 11 annual total. I was thinking maybe it would be a calculated field for 2011 and one for 2010?

               

              So in a table there would just be:

               

              Oct 10 Annual Total    X

              Oct 11 Annual Total    X

              Variance 10 -11          X

               

              Thanks

               

              Chris

              • 4. Re: Grouping Custom Date Periods
                Andy Cotgreave

                Hi Chris

                Um - that's a great question. You can manually do this by Hiding everything bar the month you want to look at, but that's pretty manual and a one-off kind of thing. I expect there is a way you can tie up parameters and filters on the table calcs - the answer is beyond me for the time being. I'll consult internally and see if anyone can come up with a solution. In the meantime - is there anyone else out there with an answer?

                 

                Andy

                • 5. Re: Grouping Custom Date Periods
                  Christopher Henderson

                  Thanks Andy

                   

                  I have got round it by creating a calculated field for 2011 and 2010 seperately:

                   

                  2011

                   

                  SUM(

                  IF [Date] >(#October,2010#)

                  THEN [Measure Select] END)

                   

                  2010

                   

                  SUM(

                  IF [Date] >(#October,2009#)

                  AND [Date] <=(#October,2010#)

                  THEN [Measure Select] END)

                   

                  This gives me the data I was after, however is there a way of saying the current month - 2 rather than me entering the month manually each month, it is the current month - 2 as our data is 2 months behind, so October will be the latest data set.

                   

                  Cheers

                   

                  Chris

                  • 6. Re: Grouping Custom Date Periods
                    Andy Cotgreave

                    Hi Chris

                    Good solution. You could replace the hard-coded months with a parameter maybe? Create a date type parameter, and use that?

                    Andy

                    • 7. Re: Grouping Custom Date Periods
                      Joshua Kitz

                      Chris there is a way to do "current month -2", you just need to use the Today() function and the Dateadd() function.

                       

                      DATEADD('month', -2,TODAY()  )

                       

                      This would currently return a full date eg: October 23, 2011

                       

                      What you actually want for your equation is 2 months before now, last year. Simply change the -2 to -14

                       

                      DATEADD('month', -14,TODAY()  )

                       

                      Now this would give you: October 23, 2010

                       

                      From the looks of your equation, your not interested in the Day of the month (or rather you want the day of the month to be the 1st). We can get rid of the day of the month by using Datename () function to get string values for the month and the year, and put a value of 1 in for the first.

                       

                      Datename('month', DATEADD('month', -14,TODAY()  ))+ " 1," + Datename('year', DATEADD('month', -14,TODAY()  ))

                       

                      This returns the string value "October 1, 2010".

                       

                      In order to change the String value back into a Date data type, use the Date() function:

                       

                      Date ( Datename('month', DATEADD('month', -14,TODAY()  ))+ " 1," + Datename('year', DATEADD('month', -14,TODAY()  ))    )

                       

                      This returns a Date value of October 1st, 2010 or 10/1/11 or however you choose to display it.

                       

                       

                      So your full calculated field would look like the following:

                       

                      For 2011 (or your 'current year' calculation)

                       

                      SUM(

                      IF [Date] > Date ( Datename('month', DATEADD('month', -14,TODAY()  ))+ " 1," + Datename('year', DATEADD('month', -14,TODAY()  ))    )

                      THEN [Measure Select] END)

                       

                       

                      For the first date value in your 2010 (really a previous year calcualtion) you simply need to change the number in the DateAdd calculation to

                      -26

                       

                       

                       

                      Hope that helps. I've been working on something similar for myself, and needed to work out how to get the 'last 12 months' in a calculated field.

                      You can also do this 'automatically' in a date filter, but in my case (and it seems like in your's) that doesn't provide enough functionality.

                       

                      What I mean by in a date filter for those who may not know:

                      1. Put a Date field in your filters

                      2. Select "Relative date"

                      3. Click the "months" tab, "last" enter "12"

                      • 8. Re: Grouping Custom Date Periods
                        Joshua Kitz

                        Edited to put the First in the calculation so it actually works. (e.g. the " 1,")

                        • 9. Re: Grouping Custom Date Periods

                          If you're still worried about this, you can dynamically hide certain months by combining the approach given below for calculating dates relative to today with a "visibility" calculated field.  Something along these lines:

                           

                          IF DATEADD('month', -1, TODAY()) > [Month of Data] THEN "Hide"

                          ELSE "Show"

                          END

                           

                          I think the logic is roughly what you want.  Anyway, then you add that dimension to your view and hide the value of "hide".  This way the past year's data is included in the table that the table calcs operate on, but...hidden.

                          • 10. Re: Grouping Custom Date Periods
                            Joshua Kitz

                            When I made my intial post/solution I overlooked the "datetrunc" function. This lets you truncate the date to the first of the month, the january first of the year, the 1st day of the first month of the quarter etc.

                             

                            The final solution I provided above can be simplified to:

                             

                            For 2011 (or your 'current year' calculation)

                             

                            SUM(

                            IF [Date] > Datetrunc('month', DATEADD('month', -14,TODAY()  ))

                            THEN [Measure Select] END)