10 Replies Latest reply on Aug 30, 2013 11:19 AM by Jim Wahl

    Moving average calculation

    C Palo

      Hello everyone:

       

      This is my challege this week:

       

      I am trying to get a moving average to calculate my baseline sales. I have used the following formula to achieve that:

       

      WINDOW_AVG(SUM([Total Sales Amount]), -4, 4)

       

      However I want to exclude the weeks which we ran an ad - so it would trully give me a BASELINE. I have second table with only five records which has the information on the 5 ads we ran during the year.

       

      If the "date" is not present in the AD TABLE I would like the WINDOW_AVG(SUM([Total Sales Amount]), -4, 4) just to work as is, but if the date IS on the AD TABLE; I would like the expression above to exclude that value.

       

      I am not very good at combining expressions - any help would be appreciated!

       

      Thanks,

       

      Cristina

        • 1. Re: Moving average calculation
          Joshua Milligan

          C Palo,

           

          This is one of those questions that probably has a relatively easy answer, but it's a little hard to say without seeing what you are working with.  Would it be possible to post a packaged workbook (even using dummy data if there's any sensitive info that can't be shared)?

           

          Joshua

          • 2. Re: Moving average calculation
            C Palo

            Joshua,

             

            Thanks for your help...please see attached the package workbook.

            • 3. Re: Moving average calculation
              Joshua Milligan

              C Palo,

               

              In looking at your data, it looks like the following calculation would determine whether a promotion was in effect:

               

              [Week Has Promotion]

              NOT ISNULL([PROMO_WKS$_Week Ended])

               

              I created that field and then used it as a filter for the view.  Since the filter is applied first, the table calculation will only be calculated for dates that did not have an ad.

               

              I've attached the workbook so you can take a look.

               

              Joshua

              1 of 1 people found this helpful
              • 4. Re: Moving average calculation
                C Palo

                Joshua,

                 

                Thank you very much for the quick response! There is one issue with the formula above - I need the baseline calculated for ALL weeks since I need a line chart of baseline (weekly). Any other good ideas??

                 

                Thanks very much!!!

                 

                Cristina

                • 5. Re: Moving average calculation
                  C Palo

                  Still srtuggling with this issue...can anyone help?

                  • 6. Re: Moving average calculation
                    Matt Lutton

                    Hi there,

                     

                    We'd love to help, but I'm not sure I understand your problem or what you are trying to do.  Jonathan was able to filter out the weeks you didn't want--what problem are you having with the resulting line chart in the workbook he posted?

                    • 7. Re: Moving average calculation
                      C Palo

                      By using a T/F I eliminate the weeks completely from the analysis. I still need to see the every single week on the charts, however the baseline calculation (which is a moving average of total dollars) should NOT include weeks where I run the add.

                       

                      For example:

                       

                      Total Dollars

                       

                      Week 11010
                      Week 21212
                      Week 399
                      Week 4 - PROMO25
                      Week 51111
                      Week 61010
                      Week 788
                      12.1410.00

                       

                      I need the BASELINE for ALL 7 WEEKS to show 10 not 12.14. and I NEED to have week 4 present in the charts with Total Dollars 25 (one line chart) and Base Dollars 10 (separate line chart).

                       

                      Is that a bit more clear?

                       

                      Thank you SOOO much for helping

                       

                      Cristina

                      • 8. Re: Re: Moving average calculation
                        Jim Wahl

                        Hi Cristina,

                         

                        Building on Joshua's formula, I think you can do this by adding a conditional on Total Sales Amount, which returns null for weeks when there was a promotion.

                         

                        Your current Baseline Calculation formula is:

                        WINDOW_AVG(SUM([Total Sales Amount]),-4, 4)
                        
                        
                        

                         

                        I created a new calculated field Total Sales Amount for Baseline

                        IF ISNULL(MIN([PROMO_WKS$_Week Ended]))
                        THEN SUM([Total Sales Amount])
                        END
                        
                        
                        

                         

                        The first part is Joshua's logic, which seems to work well.

                         

                        Now you can substitute this into a new Baseline Calculation. Since this is already an aggregate, it's just WINDOW_AVG(), not WINDOW_AVG(SUM()):

                         

                        Baseline Calculation New =

                        WINDOW_AVG([Total Sales Amount for Baseline],-4, 4)
                        
                        
                        

                         

                         

                        I also added a Lift calculation.

                        ( SUM([Total Sales Amount]) - [Baseline Calculation NEW] ) /
                        [Baseline Calculation NEW]
                        
                        
                        

                         

                        After adding this formula, make sure to change the number format to percentage (right click on the field in the left Data pane > Default Properties > Number Format.

                         

                        Here's the result in tabular format. The promotion weeks are highlighted in red. The baseline calculation is a +/- 4 week moving average. In the case of a promotion week, the value is not included in the average.

                        2013-08-30 05-23-52.png

                         

                        Jim

                        2 of 2 people found this helpful
                        • 9. Re: Re: Moving average calculation
                          C Palo

                          OMG I can't believe it worked Jim!!! I am so excited! Thank you so very much! I will be able to move all my promotion evaluations to Tableau! Thanks again!

                           

                          Cristina

                          • 10. Re: Moving average calculation
                            Jim Wahl

                            You're welcome!