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

    Moving Annual Totals

    Arnau Forne

      Hi,

       

      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,

       

      Arnau

        • 1. Re: Moving Annual Totals
          Jim Dehner

          Hi

           

          is this what you wanted

           

           

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

          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
          • 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!

             

            Arnau

            • 3. Re: Moving Annual Totals
              Jim Dehner

              Hi

               

               

              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

              Jim

              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

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

                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'

                END

                 

                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!

                 

                Arnau