6 Replies Latest reply on Feb 27, 2018 10:12 AM by Tim Enalls

    How do I create a "Rolling 12 Months" field?

    Tim Enalls

      In many instances, my company prefers that I display metrics such as volume shipped, sales orders, sales, margins, etc. in groups of rolling 12 months. We do this to ensure that whenever we analyze the performance of products, the information is always relatively current.

       

      Here's an example of what a "Rolling 12 Months" field would look like and how its values would display:

       

      What would be the best way to generate a field like this dynamically? Would it involve indexes, groups, sets, etc.?

        • 1. Re: How do I create a "Rolling 12 Months" field?
          Zhouyi Zhang

          Hi, Tim

           

          Probably you can use window_sum() function as shown below taking sales ax example:

          last 12 month

          window_sum(sum([Sales]),-11,0)

           

          or the other way by calculation field, e.g. last 12 month

          sum( if [order Date] >= dateadd('month',-12, today()) and [order date] < today() then [Sales] end)

           

          Hope this helps

           

          ZZ

          • 2. Re: How do I create a "Rolling 12 Months" field?
            Jim Dehner

            Hi Tim

            Not Certain I understand exactly what you want but see attached

             

            Your posts looks like the user to see a most recent 12 months or to go to the 12 months that preceded the most recent 12 etc.

             

            Base on that I created a parameter for the user to select the 12 month period

             

            The I put that value into a case statement

             

             

            then created the viz

             

            you can also look at other dimensions e.g category

             

            Jim

             

            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

            1 of 1 people found this helpful
            • 3. Re: How do I create a "Rolling 12 Months" field?
              Tim Enalls

              Thank you very much, Zhouyi. This brings us closer to the goal of having a calculated dimension field that lists all the rolling 12 months (full months).

               

              The reason that's so important to us is because we often build charts with rolling 12 months that could show trends of any number of metrics: sales, volume sold, # of orders, # of customers, # of products, etc. It seems only having a calculated dimension field would give us the flexibility to make these types of charts without much hassle.

               

              This is just a basic example of how we seek to use this type of calculated dimension field in Tableau on a normal basis:

              I noticed this field calculation successfully calculates the last 12 full months. How would I calculate the 12 full months before that, and before that?

               

              IF  [OrderDate] >= dateadd('month',-12, { FIXED :MAX([OrderDate])}) and [OrderDate] < { FIXED :MAX([OrderDate])} then "Last 12 Months"

              END

              • 4. Re: How do I create a "Rolling 12 Months" field?
                Tim Enalls

                Jim,

                 

                That approach has close parallels to a related use case that I was likely going to ask about at some point. I will test out your approach immediately and look for ways to adapt it to that use case.

                 

                It looks like I still need to create a field that lists rolling 12 full months as I've seen accomplished with other BI tools. From there, I would be able to create fields with rolling 6 months, rolling 3 months, and many other variations for the complex charts that require them.

                • 5. Re: How do I create a "Rolling 12 Months" field?
                  Zhouyi Zhang

                  Hi, Tim

                   

                  I think you almost there and the only I did is to complete your calculation as below

                   

                  IF  [Order Date] >= dateadd('month',-12, { FIXED :MAX([Order Date])}) and [Order Date] < { FIXED :MAX([Order Date])} then

                      "Last 12 Months"

                  ELSEIF [Order Date] >= dateadd('month',-24, { FIXED :MAX([Order Date])}) and [Order Date] < dateadd('month',-12, { FIXED :MAX([Order Date])}) then

                      "Last 13-24 Months"

                  ELSEIF [Order Date] >= dateadd('month',-36, { FIXED :MAX([Order Date])}) and [Order Date] < dateadd('month',-24, { FIXED :MAX([Order Date])}) then

                      "Last 25-36 Months"

                  ELSEIF [Order Date] >= dateadd('month',-48, { FIXED :MAX([Order Date])}) and [Order Date] < dateadd('month',-36, { FIXED :MAX([Order Date])}) then

                      "Last 37-48 Months"

                  ELSEIF [Order Date] >= dateadd('month',-60, { FIXED :MAX([Order Date])}) and [Order Date] < dateadd('month',-48, { FIXED :MAX([Order Date])}) then

                      "Last 49-60 Months"

                  ELSEIF [Order Date] >= dateadd('month',-72, { FIXED :MAX([Order Date])}) and [Order Date] < dateadd('month',-60, { FIXED :MAX([Order Date])}) then

                      "Last 61-72 Months"

                  ELSEIF [Order Date] >= dateadd('month',-84, { FIXED :MAX([Order Date])}) and [Order Date] < dateadd('month',-72, { FIXED :MAX([Order Date])}) then

                      "Last 73-84 Months"

                  END

                   

                  and then the result (using super store data source as example, it doesn't has as many month as your screenshot)

                   

                  Hope this could help.

                   

                  ZZ

                  • 6. Re: How do I create a "Rolling 12 Months" field?
                    Tim Enalls

                    Thank you, Zhouyi! Your help is going to make a tremendous difference.