3 Replies Latest reply on Nov 24, 2016 4:58 AM by Shinichiro Murakami

    Different "moving average" behaviour on different chart types, with the same data..

    Nik Sargent

      This is a follow up to my question here Stuck creating moving average of calculated percentage which seemed to be answered at first, but didn't quite get answered in the final analysis.

      I'm not sure where I am going wrong here but it is driving me crazy.


      The synopsis is this:  I want to create a moving average, I want to show it as a percentage, there are gaps in my data which affect how moving average is calculated.

      For this discussion we need to park any debate over whether averages of averages are a good thing or not..


      My data concerns call types in a contact centre, so one dimension is call type (or caller type to be more accurate), so all discussion relates to measuring outcome for each caller type.

      Another dimension is an "outcome" code. I am looking for calls of each type that have a "pass" outcome ("p"). therefore, for every caller type, I want to know what % of those caller types had a call that passed rather than "failed".


      In the earlier thread I posed the question about how to calculate moving averages when data was missing (some caller types don't call on some days).  The moving window of the average function only looks where there is data.. so, a three-day window, that has only 2 days of data, will be calculated as a 2 day moving average. This has the problem that when you sum the averages of all the outcomes for a given day, you do not necessarily get 100%  total (sometimes more, which is very confusing for the reader)...


      The suggestion was to pre-calculate the percentage of each outcome on each day with a calculated field, then calculate moving average across that percentage with a straightforward table calculation. In the end this didn't work, because the gaps in the data still remain and so the moving average window exhibits the same problem.


      However, I experimented using some calculated fields to generate "0" when there was no data, and when produced in table form this works. i.e. blank cells are filled with zero. (example in workbook)..    In the end, this simple calculated field seemed to create the right result, without inhibiting other functions (I read elsewhere you could use the "lookup" function to do something similar when data is missing, but as soon as you do, tableau then restricts you from performing further table calculations)


      zero padding.png

      So far so good, I almost wept with joy    and built a table to show the two methods side by side: one calculating from the raw data, thus not always totalling 100% and another using my calculated field, which returns 0 if there is no data - causing the averaging window always to be the right size. And this always adds up to 100% on any given day (and from what I can tell, renders obsolete any debate over averaging averages)


      table with gaps:

      table with gaps.png

      table without gaps:


      table without gaps.png


      If you plot this data using bars, it does what you expect - there are slight variations in the bars (i.e. moving average) where the in-built averaging function takes a smaller window due to missing data, vs. the one where the data is zero padded.  Again, I have a sheet to show this:  (here is the CallTypeV showing the "V" outcome... )


      moving average bars.png


      However, if you then plot the data using a trend (line) graph, which is what I need to do, something odd happens. Both methods revert to generating the same output and do not always add up to 100% - the lines become identical, even though one is based on data with zero-padding.  I am scratching my head over this...


      Here's what the line charts do:


      you'll see that for each outcome there a two lines (one supposedly based on the data with gaps, and one based on the zero-padded data) but they are exactly the same.. :-/


      moving average lines.png


      I'm flummoxed... How do I get the line charts to do what the bar charts did and show that the two calculation methods are different?

      (this is version 9.2.5 (OEM))