5 Replies Latest reply on Nov 16, 2015 1:51 PM by Keshia Rose

    Calendar Day Moving Average - Continuous

    David Hines

      Hi All,

       

      I would like to create a calendar day moving average as seen below.  I am able to perform this through the utilization of discrete pills for day and month while using the advanced table calculation dialogue.  My problem is that the end result of using discrete pills results in a discrete measure.  So when i attempt to make this a line chart, it's broken for every day.

       

      I'm able to get to this level of detail through LOD calcs, such as FIXED, although it's not a dynamic calculation with any quick filters i may apply. 

       

      Is there a way to get this same outcome on the far right column below as a continuous measure?

       

      DateDay of MonthMonthValueCalendar Day Moving Avg (3 month)
      04/01

      1

      April

      6

      4
      05/011May34
      06/011June34
      04/022April127
      05/022May67
      06/022June37
      04/033April33
      05/033May33
      06/033June33
        • 1. Re: Calendar Day Moving Average - Continuous
          Keshia Rose

          Hi David,

           

          You will not be able to create a continuous date line graph because your dates are out of order. While this may be what you need for your calculation, the result will have to be ordered correctly in order to have a continuous line. That being said, if you need to keep the order for your calculation to work, you can still create a line graph with the discrete dates. Just make sure that you are only keeping one date field on columns, and not the date, and the day and the month. If you need to see all of that information, you may consider creating a combined field. I've attached an example with just the plain numbers to show how you can do this. Let me know if this is not what you were looking for or if it doesn't work for you!

           

          Take care,

          Keshia

          • 2. Re: Calendar Day Moving Average - Continuous
            David Hines

            Thanks for your reply Keshia.

             

            I'm currently on desktop version 9.0, it appears your workbook has been created on a later version of Tableau and therefore I'm having trouble opening it.

             

            Is there a way to enable Tableau to know the order of my dates?  I recall seeing this order issue highlighted in Noah Salvaterra's Zen Master session with the drawing he made of the MGM lion at TC15.

             

            Regarding the proposed solution of using discrete dates - this chart will be one of many on a dashboard.  We've been using a standard of continuous dates on any of our time series charts.  Is there a way get to the outcome desired, while still retaining the same formatting that a continuous chart would otherwise have?  Specifically, I'm referring to how the axis and tick marks are designed.

             

            Thanks again,

            Dave

            • 3. Re: Calendar Day Moving Average - Continuous
              Keshia Rose

              Hi Dave,

               

              Here's a second try for that workbook in 9.0. I've also included an example of how you could use an INDEX() to create a continuous axis for your dates. Please note however that you won't be able to see the actual dates show up an the x-axis. The only way to do that, in the unordered fashion you are looking for, is to use them discretely with a line. Is it necessary to have the dates unordered? Or is this only to achieve the calculation you are looking to do? In other words, would it be ok if the dates were in order but the calculation ran the same way?

               

              Also, the method described in Noah's session uses x and y coordinates, and the axes still need to be in order. The ordering being referred to there is the order that determines which point to to go to next when drawing a line, but the points are still on a continuous axis.

               

              Hope this helps.

               

              Take care,

              Keshia

              • 4. Re: Calendar Day Moving Average - Continuous
                David Hines

                Thanks again Keshia, this is helpful.

                 

                In terms of my date ordering on my source - I'm using an extract from an Oracle DB.  Would I perform this sorting/ordering when building/publishing the extract?  Or is this sorting for a field within the workbook?

                 

                Thanks,

                Dave

                • 5. Re: Calendar Day Moving Average - Continuous
                  Keshia Rose

                  Hi Dave,

                   

                  I would just sort the field within the workbook, that should do it!

                   

                  Take care,

                  Keshia

                  1 of 1 people found this helpful