12 Replies Latest reply on Nov 24, 2016 1:33 AM by Nik Sargent

    Stuck creating moving average of calculated percentage

    Nik Sargent

      This problem is currently driving me crackers in Tableau Desktop v9.2.5. I've googled to try and find a solution, but haven't (or haven't been able to recognise it). I'm a newbie to tableau of about a month.

       

      It's the problem of creating a moving average of a percentage.

       

      My raw data is just counts of events (think "error codes" or "outcomes") by day , so I am interested in the % split of "outcomes" on each day, to build a trend line. 

      I can use the built in table calculation functionality of tableau to calculate a percentage for each Outcome calculated by Table-down..  That works beautifully to tell me the split of outcomes.

       

      Then I need a moving average.   So, initially I used the built in table calculation dialogue to create that. It requires me to specify the calculation type as moving->average, but this (of course) is based on the outcomes (i.e. the raw data) - i.e. absolute numbers, not the % proportion, which was calculated.

       

      So, I set a secondary calculation to be performed on % of total, and eventually managed to get the partitioning set up so that it all looked pretty good.

       

      Except it isn't and it's taken me a few days to work out why. Really this calculation needs reversing, in my use case.

       

      What tableau is doing is, for each outcome (i.e. row)  is taking an average for row outcome over the moving time window (say 3 days). So far so good. 

      It is ALSO doing that for the total (i.e. notionally the column total).   So the first observation is - because I have gaps in my data - this leads to the sum of all averages (i.e. the 3 day average in each row) not adding up to the total (i.e. the 3 day average of each day's sum).   (I have also modelled all this in excel to double check & reverse engineer, and get the same answer)

       

      If you then add the secondary calculation for percentage, Tableau calculates down the column, and since the column doesn't total properly, the percentages don't (necessarily) add up to 100%.

       

      I have gaps in the table where on certain days certain outcomes did not occur, so there is no record, and (like Excel) the tableau average function is only working the number of values that exist, not the number of days in the moving window.

       

      There are two solutions I am looking for and either would probably do, even though they give different answers. The first is to somehow account for the missing data in the averaging of the moving window. Toggling the "Null if not enough values" option doesn't do this.

       

      The second would be to essentially reverse the way the calculation is made and generate the percentages on the outcomes for each day first, and then have the moving average calculated just against those percentages...    This would work in my use case, because those percentages are essentially just KPI scores, and they may as well just be a number from 1 - 10. So even though they are a percentage, they essentially are just an index number. So averaging them is meaningful in this use case..

       

      I could make a case for either solution but I am struggling to create them...

       

       

      my numbers look like this (reproduced in excel):

            

            

      Date15-Aug-1616-Aug-1617-Aug-16Average
      Total -> 11231817.33333333
      P4413
      V7161613
      A211.5
      T11
      Y00

       

       

      and the various methods for calculating an average % over those three days (based on the above data) yield this:

       

      total112318left (average of % across)table-down sum (tableau)proportion of self
      P36%17%6%19.8%17.3%16.2%
      V64%70%89%74.0%75.0%70.3%
      A0%9%6%4.8%8.7%8.1%
      T0%4%0%1.4%5.8%5.4%
      Y0%0%0%0.0%0.0%0.0%
      ...total100%107%100%

       

      It should be noted that if you complete the blank cells with zeros, then the tableau result becomes the same as the "proportion of self" result and totals 100% - ie the rightmost two results become the same.  To be fair, I would probably prefer the "left (average of % across)" result, as it is simply the mean of the daily percentages, and for this use case, it is probably a better measure.

       

      Any help would be appreciated

       

       

       

       

       

       

       

       

       

       

       

       

                                                                                                                    

      total112318left (average of %)table-down sumproportion of self
      PNN36%17%6%19.8%17.3%16.2%
      VNN64%70%89%74.0%75.0%70.3%
      ANN0%9%6%4.8%8.7%8.1%
      TNN0%4%0%1.4%5.8%5.4%
      YNN0%0%0%0.0%0.0%0.0%
      total100%107%100%
        • 1. Re: Stuck creating moving average of calculated percentage
          David Li

          Hi Nik, this sounds like an interesting problem! Can you share a packaged workbook or at least a data sample (e.g. in Excel) so we can play around with it?

          • 2. Re: Stuck creating moving average of calculated percentage
            Nik Sargent

            HI David,
            I will do... I think this is easier to see in excel, and also I can annotate my calculations, so I will have a crack at doing that and post it in due course..It's probably much easier to understand with some data   Many thanks for your interest.

            nik

            • 3. Re: Stuck creating moving average of calculated percentage
              Nik Sargent

              HI David,

              here is an excel workbook with the key data and some explanation of what I think is going on..

               

              nik

              • 4. Re: Stuck creating moving average of calculated percentage
                David Li

                Hi Nik, sorry about the wait! I've been a little swamped recently. I took a different approach from what you described. I started with a record-level percentage calculation using a LOD calc:

                [Value]/{FIXED [Date]: SUM([Value])}

                This allowed me to use just one table calculation, which was just a Moving Average Quick Table Calculation. Here are the results:

                Does this look right, or am I missing something?

                 

                Tableau 9.3+ TWBX attached.

                1 of 1 people found this helpful
                • 5. Re: Stuck creating moving average of calculated percentage
                  Nik Sargent

                  Hi David,

                  many  thanks for the reply and apologies for the delay with mine (out of office on Friday)..

                   

                  At first glance (without dipping into teh workbook) this looks just what I was looking for - the key thing I think it nails (which I struggled with) was calculating percentages "on the fly" so that I could then apply the standard "moving average" table calculation... so essentially you have come up with the method I was looking for...

                   

                   

                  I will take a look into the work book and see what  you have done; because in fact my *actual* data needs slicing by another dimension, so I will have to figure out how to apply that to the formulas you have used (didn't want my use case to be too complex to start with).

                   

                  I am also curious to figure out (thought with less urgency) if the other case can be solved. I.e. if null data can be treated as zero, so that the built in averaging functions always apply across the whole window rather than just across non-null values.... 

                   

                  Notwithstanding the fact that that method of averaging (actually, possibly both methods) is causing me a slight philosophical crisis, because nulls represent "something that did not happen", so attributing any kind of value is perhaps questionable.

                   

                  Or to put it another way, if something performed at 100% one day, and then on the next day did not even occur, actual performance can indeed only be said to be 100% and not 50%. I'm sure i'll have a few more sleepless nights over this one...

                  • 6. Re: Stuck creating moving average of calculated percentage
                    Nik Sargent

                    Ahhh... I cannot load it because I only have tableau 9...

                    • 7. Re: Stuck creating moving average of calculated percentage
                      Nik Sargent

                      HI David,

                       

                      Even though I can't load the sheet (I have Tableau 9.2.5) I played around with your formula, tearing my hair out, until I realized I also have an excluded line (data row) in my sheet....    how can I accommodate that requirement also? If I excluded it from the table of percentages, it is not excluded from the calculated total. Basically I need to eliminate those lines on the fly, and neither count them, total them nor display them...

                       

                      thanks

                      nik

                      • 8. Re: Stuck creating moving average of calculated percentage
                        David Li

                        Is that data excluded using a filter? If so, changing that filter to a context filter will make it affect the denominator (the calculated total).

                         

                        And as for the situation where you have additional dimensions in the level of detail, you have a couple of options. One, you can add that dimension into the dimensionality argument of the LOD calculation, so it'd look like:

                        [Value]/{FIXED [Date], [Dimension2]: SUM([Value])}

                        This is easy, but a bit inflexible, since you wouldn't be able to add or change dimensions; the choice would be fixed. If you want to be able to switch them in and out on the fly, then you should probably switch to use EXCLUDE instead of FIXED for your LOD:

                        [Value]/{EXCLUDE [ID]: SUM([Value])}

                        This will essentially calculate a total value for aggregations that are broken down like the cells in your sheet, except that they won't break down by ID. Also, if you use EXCLUDE (or INCLUDE), you won't need to change any filters to context filters, because those keywords cause LODs to be calculated as aggregations and not at row-level within the context.

                        1 of 1 people found this helpful
                        • 9. Re: Stuck creating moving average of calculated percentage
                          Nik Sargent

                          Hi David,

                          sorry I am slow to respond (workload and all that...)

                           

                          Yes, my data is filtered with a regular filter. I did read on the help that a context filter gets executed first, but as yet, I don't know what one of those is, so I need to find out

                           

                           

                          Also, I read the pages for INCLUDE and EXCLUDE and could not make sense of what it was trying to tell me, so again, I need to deep dive on that. I figured out the "fixing" to an additional dimension - that is very neat...

                           

                           

                          You are right about the inflexibility - I need to drive that out of all my solutions, so they are more dynamic; so again, I will try to reverse engineer your further examples.  It's a pity that worksheets don't have any commentary area (rather than just annotations) as it would be very useful to have some background notes on sheets on how and when they can be used and how they are constructed.. perhaps this is a feature of v10?

                           

                          many thanks again - this has been a massive help

                          • 10. Re: Stuck creating moving average of calculated percentage
                            Nik Sargent

                            Hi David,

                            I thought I had nailed this and I managed to mimic your solution. Discovering context filters, too, has been a great revelation..

                             

                            However, all is not well :-)

                             

                            I'm not sure how you output the last line of your table in the example, but it is not correct
                            If you add up, for example, the pane with 8.7, 26.88 etc.  It comes to 106.53%...      So, the moving average numbers are still suffering from the fact that tableau excludes the nulls from its denominator in the averaging process..   indeed, the 8.7 and the 4.35 have no data next to them, so it comes as no surprise that if you WERE to average them with the previous field, i.e. divide them by 2, then add them up, you get 6.525  (so, accounts for the discrepancy, within rounding error)..

                             

                            So, in fact, it goes back to the question - is there a way to force tableau to include the null values in its moving window as if they were 0 values.. ???

                             

                            Thanks, nik

                            (and sorry to re-open this when it seemed done )

                            • 11. Re: Stuck creating moving average of calculated percentage
                              Nik Sargent

                              I would now like to add to this, that I raised a followup thread here Different "moving average" behaviour on different chart types, with the same data..  which essentially answers this question for this use case...

                               

                              in short, you can create a calculated field for the raw measure, which uses IFNULL to generate the correct sum or Zero if there is no data. From there, you can then simply use the inbuilt moving table calculations and secondary calculation to generate a moving average percentage....   You also have to be aware of discrete vs. .continuous - see the link for more detail... 

                               

                              nik