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

# Replicate multi-variable Excel moving average calculation in Tableau

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!

• ###### 1. Re: Replicate multi-variable Excel moving average calculation in Tableau

HI Chuck,

the normal way of moving average is relatively easy, but in your case,

the difficulty is how to make up non-existing value.

It's quite difficult to recognize non-existing cell in Tableau.

I reshaped data with scaffold method.

Join with calculation as "1"

Step 1

Step 2

Step 3

Thanks,

Shin

1 of 1 people found this helpful
• ###### 2. Re: Replicate multi-variable Excel moving average calculation in Tableau

Brilliant! Thank you very, very much, Shin. This is an elegant solution and you provided it to me so quickly and with such great visuals and instructions that even as a new Tableau user I was able to follow along and get it done. I am very grateful. However, there was one thing that did not work for me quite the way that you did it, but I found a work-around. Nonetheless, I’m curious why the way you showed it did not work the same for me. Here’s where I ran into the issue (except that I was using some data that was not masked):

First I got up through this step, shown in your demonstration as follows:

My output, using different input data, then looked like this:

So far so good. Then I executed the following step, as shown in your image as follows:

Here is my equivalent version of that step:

When I did that, I got something that looked like what you showed in the following image:

The output using my data then looked like this:

Looks like it matches your example in terms of structure. However, when doing some spot checks, I found that the computations weren’t right. I was able to establish that rather than calculating the moving 13 week sum from top to bottom across the weeks (equivalent to the 3 week sum that you demonstrated) as was the result in your demonstration, in my case Tableau did the summation calculation from left to right, across the part numbers and customers, instead. This is apparent in part by noting that the first 12 columns are blank, as one would expect if it were summing from left to right. Then I noticed in the editing pane for the calculated field (see both your example and mine, above), that it explicitly says ”Results are computed along table (across)”. When I read that, it seemed logical to think that the calculation therefore would be getting executed from left to right, rather than top to bottom, just as my spot check confirmed. As such, I took a further step and re-organized my view so that a left-to-right calculation would work. My reorganized view looks like this, and when I did it this way, I got the desired results:

So while this worked, I’m curious as to why your example delivered a top-to-bottom calculation instead, even though Tableau seems to be saying that it was going to sum your view from left to right? And why didn’t it work the same way for me? I’d like to know so that I understand how Tableau thinks. I thank you again for your help!

Sincerely,

Chuck

1 of 1 people found this helpful
• ###### 3. Re: Replicate multi-variable Excel moving average calculation in Tableau

HI Chuck,

It's quite difficult to investigate without seeing any data.

In general, table calculations are very fragile and adding, removing ,moving dimension easily change the results.

And without seeing real file, it's impossible to suggest right process.

If you have specific example please post that as another thread with attaching twbx.

To close this thread, could you please mark my answer as correct from original post, not from inbox.

Thanks,

Shin.

• ###### 4. Re: Replicate multi-variable Excel moving average calculation in Tableau

Unfortunately, it's not data I can share, so I'd have to create a new workbook in order to get your help with it directly. Perhaps I will, but I can't right now. For now, what you've shown me plus the work around you provided have accomplished what I needed. Again, thank you, Shin.

• ###### 5. Re: Replicate multi-variable Excel moving average calculation in Tableau

Hi Chuck,

I understand, to make the support efficient, having the actual data/file is the most important key.

BTW, this post will be refereed from all the community members, and could you mark "My" answer as correct instead of yours to avoid viewers' confusion.

Thanks,

Shin

• ###### 6. Re: Replicate multi-variable Excel moving average calculation in Tableau

Hi Shin,

I must say that I find the post format confusing. I’m looking for the place to mark your answer as correct, but I can’t see it.  Here’s what I am seeing:

The only place I can see to mark anything as correct is under my follow-on post, as shown here:

Also, I can’t see a button for save my rating or my “correct answer” reply, unless I am simply supposed to reply to the post again rather to my marking specifically.

Thanks,

Chuck

• ###### 7. Re: Replicate multi-variable Excel moving average calculation in Tableau

Hi Shin,

I must say that I find the post format confusing. I’m looking for the place to mark your answer as correct, but I can’t see it. The only place I can see to mark anything as correct is under my follow-on post. Also, I can’t see a button for save my rating or my “correct answer” reply, unless I am simply supposed to reply to the post again rather to my marking specifically. Please advise and I will be happy to do so.

Thanks,

Chuck

• ###### 8. Re: Replicate multi-variable Excel moving average calculation in Tableau

Hi Chuck

Yes, the navigation is not very user-friendly,

Nothing can be done from inbox view, go to original post first.

Unmark as Correct answer, then you will see the button on other posts.

Thanks,

Shin

• ###### 9. Re: Replicate multi-variable Excel moving average calculation in Tableau

Thanks for the reply, Chin. I followed your instructions and clicked on the button under your answer, however I didn’t subsequently see any button to confirm, save, record, or update the post to ensure that my selection took. As such, I simply closed the browser after making the selection. Please confirm that both my “correct answer” reply and my 5-star rating have been recorded. Thank you.

Chuck

• ###### 10. Re: Replicate multi-variable Excel moving average calculation in Tableau

Hi Chuck

Thank you, and Yes.

Shin

• ###### 11. Re: Replicate multi-variable Excel moving average calculation in Tableau