1 2 Previous Next 18 Replies Latest reply on Jul 16, 2018 9:16 AM by Jon Boschee

    Moving Average

    Jon Boschee

      Hi everyone,

       

      I'm trying to create a 6 month moving average for average unit price.  I want it to look at the past 6 months of sales and divide it by the past 6 months of units sold.  Year and month are in 2 separate columns and I used DATE(STR([Month])+"/1/"+STR([Year])) to create a Date to reference. 

       

      My average unit price calculated field is sum(sales)/sum(units).  This comes out correct for the month, but when I try to add table calculation it is taking the average of the average unit price of the past 6 months instead of taking the past 6 months of sales/past 6 months of units.

       

      Do I need to be using Lookup to get the right data?

       

      Thanks in advance!

        • 1. Re: Moving Average
          Simon Runc

          hi Jon,

           

          Yes this one can be a tricky one!

           

          So if you use the Quick Table Calculation for Moving Average, and set the Months to 6 back...you can then drag the pill into the Measures pane, as you can see the formula Tableau has built (you also get a chance to rename it and make it re-usable)

           

          [Average Price Rolling Default]

          WINDOW_AVG([Average Price], -6, 0)

           

          So it's doing as you describe. However we can adapt the logic to make it work how you want!

           

          [Average Price Rolling Aggregated Sales and Qty]

          WINDOW_SUM(SUM([Sales]), -6, 0)

          /

          WINDOW_SUM(SUM([Order Quantity]),-6,0)

           

          Hope that makes sense, and helps

          1 of 1 people found this helpful
          • 2. Re: Moving Average
            Jon Boschee

            Hi Simon,

             

            Thanks!  It's working as long as it doesn't have to cross into the previous year.  If I'm looking at May 2018, it's not picking up December 2017.  Is there a way to have it consider the months in the previous year?

            • 3. Re: Moving Average
              Simon Runc

              hi Jon,

               

              So yes we should be able to do that, but it will depend on the arrangement of pills you have, on how we set this up. Can you post and example of your viz? (or adapt the one I have posted, so it reflects your real world problem)

              • 4. Re: Moving Average
                Jon Boschee

                Hi Simon,

                 

                I put in some dummy data and the My Attempt column is what I got.

                • 5. Re: Moving Average
                  Simon Runc

                  hi Jon,

                   

                  So I think the problem isn't one of the changing year, so much it's picking up too many months!

                   

                  at the WINDOW_SUM(SUM(...),-6,0) is inclusive it's actually picking up 7 months, not 6...so we just need to change the -6 to a -5.

                   

                  In the attached I've brought the 2 Rolling Sums into the Table

                   

                   

                  and if we look at the months back to December for Sales

                   

                   

                  It seems to doing it correctly (with the change to -5)

                   

                  Hope that's it, but let me know if not.

                  • 6. Re: Moving Average
                    Jon Boschee

                    Hi Simon,

                     

                    I changed it from -6 to -5 and it works great except when it needs to look at 2 different years.  If I'm looking at May 2018, then it will only look at Jan-May 2018 and not include Dec 2017.  Not sure what's causing it, but that seems to be the issue.

                    • 7. Re: Moving Average
                      Simon Runc

                      hi Jon,

                       

                      So it looks, to me, like it is going across years.

                       

                      If you look at the example workbook I posted the Sales from December 2017 to May 2018 is 4,737,045 and the WINDOW_SUM(SUM([Sales]),-5,0) adds up to that. If you can let me know the Running Sales Sum from December 2017 to May 2018 (inclusive) should be, I can look at why it's not doing what you expect.

                      • 8. Re: Moving Average
                        Jon Boschee

                        Hi Simon,

                         

                        I get 3,901,500 for sales for Dec 17 to May 18.  It's not grabbing Dec for some reason.  Not really sure why.

                        • 9. Re: Moving Average
                          Simon Runc

                          You mean in your real world version?

                           

                           

                          if you look at the one I posted, I have 4,737,045 for May for the Moving 6 Months Sum of Sales. Do you have any other dimensions in your Viz? In you look at the "Compute Using" on the Table Calculation it's set to Table Down, meaning it starts once at the start of the table, and keeps going. If I had the compute using on 'Month of Date' it would restart every new year (and country, in fact)

                           

                          To be double sure, you can set it up like this (in Edit Table Calculation), where you set it to specific dimension and tick everything in the list (anything not ticked, means start the calculation again for every X)

                           

                           

                          Let me know if that doesn't do the trick

                          • 10. Re: Moving Average
                            Jon Boschee

                            Hi Simon,

                             

                            I have the same set up for my table calculation.  I think it has something to do with my dates. 

                             

                            I'm pulling in Sales with:

                             

                            If [Year] = [Year from Exchange Rate]

                            then

                            [Net Sales]

                             

                            I think this is causing my moving average to only look at 1 year.  What do you think?

                            • 11. Re: Moving Average
                              Simon Runc

                              could well be. What is [Year from Exchange Rate]?

                               

                              If that's a single year (say it's a parameter, set to 2018) then your calculated net sales only contains data from 2018

                              • 12. Re: Moving Average
                                Jon Boschee

                                It's the exchange rate for each year to convert local currency to usd.  I'm not using it as a parameter though.  I think that it's part of the issue.  It looks for the year of the exchange rate and then only returns the sales for that year, but it populates every month for each year.  Should I be using a lookup to get the right sales and units?

                                • 13. Re: Moving Average
                                  Simon Runc

                                  So what does your data look like?

                                   

                                  Is the correct exchange rate, not in the same row for the year of that sale? It looks like a row level calculation, so if you data looks something like this

                                   

                                   

                                  Your "Your Calculation" doesn't contain any sales for 2017, so doesn't matter how far it goes back

                                  • 14. Re: Moving Average
                                    Jon Boschee

                                    The exchange rate isn't working right in my file.  It's taking an average of all the years for a country.  So my data setup is probably not correct which is why the table calculation is off.

                                    1 2 Previous Next