4 Replies Latest reply on Jan 15, 2018 4:00 AM by Colm Murphy

    Calculating Moving Average Outside of Window

    Colm Murphy

      Hi All,

       

      I'm trying to calculate a moving average outside of the window in Tableau. In the attached workbook, I have set up the table calculation as normal, however it is using the values in the (as it is supposed to do). The reason that this is incorrect for me, is because I have a LOD function set up to calculate the average before it goes into the view (this is due to how the data is set up - this is survey data, where we had to pivot the rows to get our %s and filter off different brands).

       

      This means the moving average is being calculated from the individual averages - which is incorrect. I've been trying to use the LookUp function, however it isn't playing nice with the LOD function already set up. I'm not sure if that angle is the best way to proceed or if we need to rethink how we get our averages.

       

      Any insight on this would be much appreciated.

       

      Colm

        • 1. Re: Calculating Moving Average Outside of Window
          Shinichiro Murakami

          Hi Colm,

           

          It's difficult to understand what is your issue.

           

          Could you explain your issue and the expected results with real sample numbers.

           

          Just tried something.

          Changing the formula as below brings attached results.

           

           

           

          Again, pick up some example and tell us "is" and "should be".

           

           

          Thanks,

          Shin

          • 2. Re: Calculating Moving Average Outside of Window
            Colm Murphy

            Hi Shin,

             

            Thank you for responding! I'll try and explain things further. Using the Moving Average table function, the moving average is being calculated using the percentages in the window, rather than the original records (to clarify: I thought I renamed the W Hyhey variable to "weighted number of records" - that's what this variable represents). This is survey data, and the percentages represent the number of people who gave each brand a score of 4/5 or 5/5.

             

            So, using "Brand 8" as an example in the "Satisfied %4/5 Moving Average" tab, I set the moving average function to calculate using the previous 3 quarters, so the calculation uses: (Q1 + Q2 + Q3 + Q4) / 4, which is (84.796% + 85.776% + 84.756% + 88.292%) / 4 = 85.906%.

             

            However, the above percentages are already averages, so it is incorrect to calculate the moving average from them.

             

            So, what I would like is for Tableau to instead calculate the moving average using the number of records (the W Hyhey variable). The above %s for Brand 8 were calculated using this variable, and the data for each is (first number is number of people who gave a 4/5 or 5/5 score, second number is total number of people):

             

            Q1= 49 / 58 (= 84.796%)

            Q2= 39 / 46 (= 85.776%)

            Q3= 47 / 56 (= 84.756%)

            Q4= 51 / 58 (= 88.292%)

             

            So the correct moving average for Brand 8 should be (49 + 39 + 47 + 51) / (58 + 46 + 56 + 58) = 186 / 218 = 85.321%.

            (note: due to the weighted survey data being used, Brand 8 will actually come out as 187 / 218 = 85.78%).

             

            The problem I believe is that in order to calculate the averages for each quarter, and be able to chart them all in the same view while differentiating by brand, we had to pivot the data and use an LOD function. Due to how Tableau orders its functions, the Moving Average function is being calculated after the LOD function, and so we get the average of the averages.

             

            I apologise for the wall of text, but hope that I have explained the problem in a little more detail.

             

            Kind Regards,

            Colm

            • 3. Re: Calculating Moving Average Outside of Window
              Shinichiro Murakami

              Hi Colm,

               

              I'm still not sure I understand your request correctly or not, but focused on crating 85.78% for Brand 8.

               

               

              Let me know if you need any further help.

               

               

              Regards,

              Shin

              • 4. Re: Calculating Moving Average Outside of Window
                Colm Murphy

                Hi Shin,

                 

                That's along the lines of what I am looking for, however I am looking to get this calculated for each quarter - you got it working for Q4 now, however I'd like to do the same for Q2 and Q3.

                 

                I've attached the workbook again with some additions - In the [*Test] functions I've calculated the running total of respondents for each Quarter - what I'd like to do is get these values as percentages of the total.

                 

                For Brand 8 Q3 this is 48.91 + 39.45 + 47.25 = 135.61

                For Brand 8 Q4 this goes 48.91 + 39.45 + 47.25 + 51.47 = 187.08

                 

                I'm looking to get the percentage of the total for each of these as a way of getting the moving average.

                 

                In the [*Test %Q18weighted] variable I tried substituting the [W Hyhey] variable for the [*Test] function, however Tableau can't do a table calc before an LoD calc.

                 

                I hope my objective makes a bit more sense now, and I appreciate the time you've taken to look at it already!

                 

                EDIT:

                 

                I think I got it! It is quite messy but it seems to be working:

                 

                I went back and created new calculated fields, similar to the [*TEST] fields above but using the LoD function from [%Q18weighted], and was able to use the LookUp function with that. That allowed me to calculate the totals for preceding quarters ([*TEST TOTAL] fields), and then I simply divided the [*TEST] field by the [*TEST TOTAL] field.

                 

                The Brand 8 percentage is 85.93% - I know I said 85.78% earlier, which is what you calculated, however I was wrong earlier, as that was using rounded numbers (which you noticed and included in your previous solution).

                 

                Thanks again for looking at it Shin.

                 

                Kind Regards,

                Colm