4 Replies Latest reply on Oct 4, 2018 1:30 AM by Arnau Forne

    Moving Annual Totals

    Arnau Forne



      I need to create Moving Annual totals for each month in order to create a chart like the one shown below.

      The MAT periods show become Dimensions as later on I need to add them in a Parameter along with Months, YTD, etc


      In the database I only have months and sales and what I'm missing is to be able to create the MATs (green bit)



      I attached the excel file.


      Thanks in advance,



        • 1. Re: Moving Annual Totals
          Jim Dehner



          is this what you wanted



          use the formula shown and set to Null if not enough values


          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
          • 2. Re: Moving Annual Totals
            Arnau Forne

            Hi Jim,


            It works, thanks! Veru useful!


            The next step, if possible, would be to transform this Measure into a Dimension. This way I would be able to use it as a Date dimension and add it in a Parameter along with Year, YTD, Month.


            I guess here the question is whether we can also create a Rolling 12 month as a Date dimension. Make sense?


            Many thanks!



            • 3. Re: Moving Annual Totals
              Jim Dehner




              Running totals are table calculations that are at the bottom of the order of operation - converting to a dimension is not going to work


              Image result for tableau order of operations


              so what can you do - Parameters in tableau are static values -  they don't do anything in themselves - they must be included in a filter or a calculation -

              Not certain what the real goal here -   what is it you want to show in the viz - an example would be helpful


              1 of 1 people found this helpful
              • 4. Re: Moving Annual Totals
                Arnau Forne

                Hi Jim,


                Sorry for my late reply.


                I found a solution for that.


                Step 1: Create a Calculation for Latest Date


                {FIXED: MAX([PERIODS])}


                Step 2: Calculation for MAT


                IF [MAT TEST]='MAT' THEN

                IF YEAR([PERIODS])=YEAR([Latest Date TEST]) THEN


                ELSEIF [PERIODS] <= DATEADD('year',-1,[Latest Date TEST])

                AND [PERIODS] >= DATETRUNC('year',DATEADD('year',-1,[Latest Date TEST]))

                THEN 'MAT-'+STR(YEAR([PERIODS]))

                ELSEIF [PERIODS] <= DATEADD('year',-2,[Latest Date TEST])

                AND [PERIODS] >= DATETRUNC('year',DATEADD('year',-2,[Latest Date TEST]))

                THEN 'MAT-'+STR(YEAR([PERIODS]))

                ELSE 'X'



                The only limitation I found is that this is only calculating MAT from January to July instead of providing all available months. Let me show you.



                I would like also to show from Aug’17 to Dec’17 MAT data, and even Dec’16, as my data starts at January’16.


                I guess the limitation comes from defining the Latest Date


                But I don’t know how to solve that. Can you help me with that?


                Many thanks in advance!