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

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