11 Replies Latest reply on May 14, 2018 8:49 AM by Chuck VanDam

    Replicate multi-variable Excel moving average calculation in Tableau

    Chuck VanDam

      Hello,

       

      I am new to Tableau and am trying to figure out how to use Tableau to replicate a moving average calculation created in Excel with either a SUMPRODUCT or array function. The problem that needs to be solved is this:

       

      We have a set of products (Product A, Product B, etc.) that are sold to customers (Customer 1, Customer 2, etc) over a period of days. We have a record of aggregate sales by day, customer, and product that looks something like this:

       

        ABCDE
      1DateCustomerProductSales3-day Average
      21-Oct-17Customer 2Product B$6,618
      31-Oct-17Customer 1Product A$7,102
      41-Oct-17Customer 2Product A$21,924
      52-Oct-17Customer 2Product B$5,976
      62-Oct-17Customer 1Product B$6,823
      72-Oct-17Customer 1Product A$11,944
      82-Oct-17Customer 2Product A$21,417
      93-Oct-17Customer 1Product A$22,817
      104-Oct-17Customer 2Product A$7,815
      114-Oct-17Customer 1Product B$10,561
      124-Oct-17Customer 2Product B$16,975
      135-Oct-17Customer 2Product A$3,893
      145-Oct-17Customer 1Product A$10,641
      156-Oct-17Customer 1Product B$12,913
      166-Oct-17Customer 2Product B$13,662
      176-Oct-17Customer 1Product A$17,174
      187-Oct-17Customer 2Product A$9,628
      197-Oct-17Customer 1Product B$12,357
      207-Oct-17Customer 1Product A$15,532
      218-Oct-17Customer 1Product B$3,060
      228-Oct-17Customer 1Product A$7,899

       

      What we need to do is to calculate a moving average every day for each product by customer. In other words, for each day we want to answer the question "What were the average sales of Product B sold to Customer 1 over the past 3 days?" The approach I would take in Excel would involve either a SUMPRODUCT or array function like this, in this case starting in cell E9 (a 3-day runway is needed):

       

       

      Calculation using SUMPRODUCT:
      =SUMPRODUCT(($D$2:$D$22)*($C$2:$C$22=C9)*($B$2:$B$22=B9)*($A$2:$A$22<=A9)*($A$2:$A$22>=A9-2))/3
      Alternative array calcultaion:
      {=SUM(($D$2:$D$22)*($C$2:$C$22=C9)*($B$2:$B$22=B9)*($A$2:$A$22<=A9)*($A$2:$A$22>=A9-2))/3}

      I would be grateful for any available help. Thank you!