5 Replies Latest reply on Jan 12, 2016 11:10 AM by Carl Slifer

    Moving average of filtered measure(s)

    Lorenzo Carlisle

      I am working with some email data and would like to plot a moving average of any measure(s) selected in a filter.

       

      For example, if I select "Opens", I would like a plot of the moving average of Opens (i.e. average opens over past 3 weeks) to overlay the plot of Opens.

       

      If I instead select, "Clicks", I would like a plot of the moving average of Clicks to overlay the plot of Clicks, etc., etc. I should also note that there are both aggregated and non-aggregated values in this pool of measures.

       

      I attempted a pretty manual solution using a Parameter Control, and calculated field that populates based on the Parameter Control's selected measure (http://kb.tableau.com/articles/knowledgebase/parameterized-measure). However, the issue there is that aggregated and non-aggregated fields cannot be combined in the CASE formula used for the calculated field. Therefore I cannot move from Opens to Open Rate on the same plot.

       

      I would appreciate any assistance and/or related tips. Thanks very much!

        • 1. Re: Moving average of filtered measure(s)
          Carl Slifer

          Howdy Lorenzo,

           

          I do not see that this should be an issue with aggregating and non-aggregating. This is because we should have two separate measures that are being triggered for the parameter. One that is either Clicks or Opens and another that is the moving average of these.

           

          TRY

          [Open or Click Field]

          CASE [Parameter]

          WHEN 'Open' THEN [Opens]

          WHEN 'Clicks' THEN [Clicks]

          END

           

          [Moving Average of Open or Click Field]

          WINDOW_AVG(SUM(

          CASE [Parameter]

          WHEN 'Open' THEN [Opens]

          WHEN 'Clicks' THEN [Clicks]

          END

          ),-3,0)

           

          If there is some issue that I cannot see please let me know At worst if say clicks are not aggregated and Opens are you can just wrap inside the statement

          [Open or Click Field]

          CASE [Parameter]

          WHEN 'Open' THEN [Opens]

          WHEN 'Clicks' THEN SUM( [Clicks])

          END

           

           

          Cheers!

          Carl Slifer

          InterWorks

          • 2. Re: Moving average of filtered measure(s)
            Lorenzo Carlisle

            Hi Carl,

             

            Thanks very much for the response. I will have to use that window_avg function!

             

            However, the aggregating/non-aggregating problem is actually that, besides whole number measures like opens and clicks, other measures are calculated rates. For example, two of the measures -- "opens" and "sent" -- are actually used to calculated a third measure, "Open Rate", via division.

             

            It is not possible to wrap something like agg([Open Rate]) inside of the case statement in the same way that it's possible to do sum([Clicks]).

             

            Does this clarify the issue?

            • 3. Re: Moving average of filtered measure(s)
              Carl Slifer

              Hi Lorenzo,

               

              The window_avg function is actually not that fancy. You can cheat too! When you create a table calculation with the 'quick table calc' option you can drag the measure off and onto the measures pane and then you can see how it is actually being calculated 'behind the scenes'. Its just how Tableau creates the moving average calculation.

               

              Okay, opens and clicks calculated field works because they are both whole number measures. But how are you calculating rates? It would guess something as simple as the number opened divided by the number sent?

               

              Why can we not use

              [This is the Rates]

              CASE [Parameter]

              WHEN 'Open' THEN SUM(Open) / SUM(Rate)   <--- Here we could actually just use the already calculated OPEN RATE field

              WHEN 'Click' THEN SUM(Clicks) / SUM(XYZ)    <--- Here we can use the already made CLICK RATE Field

              END

               

              I'm shooting in the dark from my hip here because I am not privy to your data structure. I think you're pretty close with asking about the agg([open rate]) lets chat about that. If we have a clear workbook and pull a measure onto a shelf somewhere by default Tableau wants to do a sum aggregation. It is being aggregated! But the type of aggregation is a sum, its adding everything up, no brainer right? Well we can do that with the AVG as well as the MIN, MAX. it's aggregating to a certain level already. And it aggregates to this degree by reading every row in the partition. IF we do sum by itself we add everything but if we do sum with a date field on the orders shelf it sums everything within that date. The other aggregations work the exact same.

               

              So the AGG() that you see when you put something onto the field is pretty simple to understand. It's saying its pre-aggregated and it's pre-aggregated based on what the underlying calculation wanted. So if you took SUM(X) / AVG(Y)... you know for whatever reason it would be pre-aggregated. Because it's not looking at two things in the same row but rather two columns within the same array in a partition. Its a bit tricky to understand without pointing and gesticulating at a white board but as you play with them more I think understanding WHY and WHAT is happening is more important than the I need to do X, right now. Then in the future I can quit posting here, retire on a tropical island and you get to train the next generation of padewans.

               

              Cheers

              Carl Slifer

              InterWorks

              1 of 1 people found this helpful
              • 4. Re: Moving average of filtered measure(s)
                Lorenzo Carlisle

                Carl,

                 

                Thank you for the information on how Tableau aggregates data. It reinforced my current understanding.

                 

                I think what you're saying in the bit about calculating rates is that I do not actually need to use the aggregate values (i.e. "open rate", "click rate") and can simply divide the non-aggregate values to calculate them, instead. I had not thought of that, but that will actually work! Not sure if that is in fact what you intended, but you talked me through it, regardless.

                 

                Many thanks!

                .

                • 5. Re: Moving average of filtered measure(s)
                  Carl Slifer

                  Glad to head it Lorenzo.  I'd rather give people to the tools to succeed than build the house for them each time.

                   

                  Enjoy!