4 Replies Latest reply on Feb 18, 2016 9:41 AM by pooja.gandhi

    Rolling Months

    Kushal chawda

      I have one report in which dimension is I am showing month wise sales

      Month values are jan 2016, feb 2016 etc

       

      My requirement is

      For Jan 2016, I want to display sum of sales for last 12 months (i.e from jan 2015 to jan 2016)

      For feb 2016 , I want to display sum of sales for last 12 months (i.e from feb 2015 to Feb 2016)

      etc.

       

      How can I achieve this/

        • 1. Re: Rolling Months
          Simon Runc

          hi Kushal,

           

          So one way to do this is to use the WINDOW_SUM table calculation.

           

          In the attached I've set up the following formula

          [Rolling 12 Month Sales]

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

           

          the -11 means go back 11 partitions (in Tableau language), and as the VizLoD is Month, and the Calculation it set to compute using 'Order Date' this is back 11 months (in human language!). The zero is the stop point, which is the current month.

           

          I've brought in both 'regular' sales and the new calculated field so you can see what it's doing.

           

          Hope this helps, and makes sense, but please post back if not.

          1 of 1 people found this helpful
          • 2. Re: Rolling Months
            Kushal chawda

            But what if I have another dimension in table.. lets say product?

            • 3. Re: Rolling Months
              Simon Runc

              ...So this is the beauty of Table Calculations! you are able to define how the calculation runs. Depending on how your Viz is set up you maybe able to use one of the 'default' compute-using such as 'Pane Down', but by using the 'Edit Table Calc' -> 'Advanced' you are able to select exactly how you want the calculation to run, regardless of the pill arrangement.

               

              In the attached I've added 'Customer Segment' and then set the Table Calc (using the Advanced set up) as per the below

               

              So in human-language! this says run the Calculation (adding the last 11 values) over the order date (and as we have this at the level of Month in our Viz, this is what it runs it over) and restart the calculation every Segment.

              • 4. Re: Rolling Months
                pooja.gandhi

                Simon - Love the human language/tableau language brief here!