4 Replies Latest reply on Oct 14, 2018 8:03 PM by seraj alam

    Rolling 12 Measure by Month

    Loretta Antonino

      Hi Everyone, I am trying to create a Rolling 12 measure to graph, that will move each month.

       

      I found this discussion How do I create a "Rolling 12 Months" field? but this isn't quite what I need. This discussion talks about months 1-12 and then 13-24, 25-36 and so on, giving them the 12 months to date each year.

       

      What I am trying to graph is what was the sales 12 months to October 2018 (Nov 2017 to Oct 2018) and graph the results for each month, as different points of time.  The graph would plot 24 columns, with each column representing the last 24 months, and each column containing 12 months of data. This will smooth our the data and take out the seasonal aspects.If I look at October 2018, I would see a years worth of sales and if I look at June 2018 or February or any other month, I will see the a years worth of sales to that point in time.

       

      The below example is what I am trying to do, although this example was taking from google images and is not my actual data. Each point in time in the blue data, represents 12 months worth of data (although they have averaged these, which I don't need to do just yet).

       

      Rolling-Average-12-Months-07.png

       

      Can anyone please point me in the right direction. I am new to Tableau, although have been using other BI systems for years that actually have this capability as a preset, so I have never had to build it from scratch.


      Thanks

        • 1. Re: Rolling 12 Measure by Month
          ShivaRam Chennapragada

          Drag your date to filters card and select "Relative Filter",

          Then under Months tab, click on Last _ Months radio button and type in 12. Hit OK.

           

          Your viz would look something similar.

          Hope this helps.

           

          Best,

          Shiva.

          • 2. Re: Rolling 12 Measure by Month
            Loretta Antonino

            Thanks. That doesn't seem to do what I need (although I did learn something new, which I am happy about). I appreciate your help and time. I am very new to Tableau and this system has it's only unique quirks, that I haven't experienced in other systems, which I don't yet have my head around.

             

            When I did what you said, I just got 12 months plotted on the graph, with sales for that month only in each column. I want to plot 24 months (obviously that would be just changing the relative filter to 24 instead), but with each column, I need the data to be dynamic and sum up a full year of sales, not just give me the sales that occurred in the actual month.

             

             

            For example the last 5 months of columns would have the aggregate total for the following:

             

            Jun (Jul 2017 to Jun 2018 Data)

            Jul (Aug 2017 to Jul 2018 Data)

            Aug (Sep 2017 to Aug 2018 Data)

            Sep (Oct 2017 to Sep 2018 Data)

            Oct (Nov 2017 to Oct 2018 Data)

            • 3. Re: Rolling 12 Measure by Month
              ShivaRam Chennapragada

              Hi Loretta,

               

              There's only way I could think of, using a Parameter. Here's how to do it,

              1) Select Create Parameter

              2) Then give it a name, select data type, select Range, Set From Field and load values from your date field, finally set the step size to 1 month, so you get increments of 1 month.

              3) Create a Calc field to activate this parameter,

               

              4) Give the calc field a name, and type in this formula,

              5) Now you place this calc field in filters card and select 1. (convert this dimension first.)

              6) Then Date in Columns, Sales in Text, Show Parameter and done.

               

              Attached workbook.

               

              Note: Parameters are static, which means every time you have new data flowing in you'll have to update the parameter accordingly. However, if you're on 2018.2, it comes with Extensions which enables you to use dynamic parameters.

              Tableau Extension Gallery (BETA)

               

              Hope this helps.

               

              Best,
              Shiva.

              • 4. Re: Rolling 12 Measure by Month
                seraj alam

                Hi Loretta,

                 

                You can use the parameter to get the desired result. Follow these step  

                 

                create a parameter  and name "Number of months"

                Create P.png

                 

                create P Month.png

                 

                then again create a parameter name "Select date"

                 

                Select date.png

                 

                then create two calculate field and place it to filter shelf and select true

                 

                 

                start date.pngEnd date.png

                 

                 

                Right-click the Number of months and Select date parameter and show parameter control.

                Number of Months -  parameter will allow selecting the desired number of months

                Select date  - parameter will allow you to select the month to back as desired months.

                 

                I have attached a workbook for your reference.

                 

                Loretta Antonino - pease mark this answer is "Correct answer" if it solves your problem

                 

                 

                overall.png