1 Reply Latest reply on Jan 26, 2013 7:37 AM by Joe Mako

    Combining Moving Average with Percent of Total

    Dan Meier

      Tableau provides a slick, simple mechanism for charting percent of total using the TOTAL function computed using Table(Down):

       

      SUM([FieldValue])  /  TOTAL(SUM([FieldValue]))

       

      takes the sum of each series and divides it by the TOTAL sum for ALL series for EACH  X-axis tick.  (The results, when charted with two series, always remind me of a Rorschach test!)

       

      Moving averages in Tableau are also slick and simple -- you simply wrap the SUM functions in WINDOW_SUM to allow the data from several X-axis ticks to be aggregated.  So, for example, a 6-month moving average of...

       

      SUM([FieldValue])       becomes      WINDOW_SUM(SUM([FieldValue]), -5, 0)

       

      However, calculating the moving average of a percent of total is problematic.  Using the handy "wrap in WINDOW_SUM" rule, the first formula above,

       

      SUM([FieldValue])  /  TOTAL(SUM([FieldValue]))

      becomes...

      WINDOW_SUM(SUM([FieldValue]),-5,0)  /  WINDOW_SUM(TOTAL(SUM([FieldValue])),-5,0)

       

      ...however, the results are not at all what I expect.  What I expect is the sum of [FieldValue] for each series for each X-axis tick divided by the total [FieldValue]s for ALL series for each X-axis tick (i.e., Table(Down)).

       

      What I'm getting, however, is the sum of [FieldValue] for each series for each X-axis tick divided by the total [FieldValue]s for each series for ALL X-axis ticks (i.e., Table(Across)).  I can't seem to force a verticle, Table(Down) calculation to keep the TOTAL operating vertically on X-axis ticks within the WINDOW_SUM range, and the TOTAL function isn't allowed outside the WINDOW_SUM.

       

      See the attached file for an example.

       

      Is there a solution to this???

        • 1. Re: Combining Moving Average with Percent of Total
          Joe Mako

          Dan,

           

          You have a few options and routes to get the results you are looking for. The key that you are looking for is that if you have separate calculated fields, you can have different compute using configurations for each calculated field.

           

          In the attached,

           

          1. added two calculated fields:

           

          Total:

          TOTAL(SUM([Unit Count]))

           

          Moving Average - Nested:

          WINDOW_SUM(SUM([Unit Count]),-5,0)

          /

          WINDOW_SUM([Total],-5,0)

           

          2. placed the Moving Average - Nested field on the Rows shelf.

          3. from the pill's context menu, selected Edit Table Calculation

          etc.png

          4. configured the compute using for Moving Average - Nested

          MA N cu.png

          5, changed the Calculated Field drop-down to Total, and configured its compute using

          T cu.png

           

          In Tableau, the pill that you select for compute using will be used for addressing, and all other dimension pills will be used for partitioning that calculation.

           

          This was just a light walk through, I would be glad to explain in more detail if you contact me.