2 Replies Latest reply on Sep 30, 2018 11:46 PM by Yuriy Fal

    Conditional moving average

    Francesco Cometa

      Dear all,

      I have a table similar to the following:

         

      1BCD
      2DateProfitProfit 2
      3Mar-17    12,123     12,123
      4Apr-17    34,432     34,432
      5May-17    34,432     34,432
      6Jun-17    12,332     12,332
      7Jul-17    34,342     34,342
      8Aug-17    65,656     65,656
      9Sep-17             -       37,443
      10Oct-17             -       45,814

       

       

      "Profit" column is a calculated field, with aggregated values.

      "Profit 2" column is another calculated field, which takes the corresponding "Profit" value when it is different of zero, otherwise the moving average of the previous three values of the column ""Profit 2".

      Excel formula is like this (referring to yellow cell): =IF(C9<>0,C9,AVERAGE(D6:D8)). So it's like having a moving average of the previous values of "Profit 2" whenever "Profit 1" value is zero.

      I will use it for forecasting purposes (I prefer it to forecast function in Tableau because it is more flexible for my goals).

      Do you know how to transpose this kind of formula in a calculated field in Tableau? The best should be to get moving average as parametric (i.e. deciding how many previous values to consider).

       

      Thank you so much!

      fc

        • 1. Re: Conditional moving average
          Patrick A Van Der Hyde

          Hello Francesco,

           

          It would look something like this:

           

          IF Sum([Profit])>0 then sum([Profit]) else window_avg(sum([Profit]),-3,-1) End

           

           

          I have attached a sample workbook showing this example.

           

          You did not state the version of Tableau in use so I saved this as a 10.2 version of Tableau.

           

          I hope this helps. 

           

          Patrick

          • 2. Re: Conditional moving average
            Yuriy Fal

            Hi Francesco,

             

            What you're asking for is a recursive calculation --

            the one that refers to its own result(s) obtained in the previous step(s).

             

            The only recursive function in Tableau is the PREVIOIUS_VALUE() --

            a part of a Table Calc that refers to its own result from a Previous Cell in a Partition.

             

            The PREVIOUS_VALUE() function makes it possible

            to calculate RUNNING aggregations in a single pass.

             

            The notion of 'single pass' is important to understand.

            Tableau calculation engine has no iterative (multi-pass) mechanisms

            available (visible) to the user -- though it may iterate internally

            inside its functions calls (as every calculation engine does).

             

            So the PREVIOUS_VALUE() function can not use its own results calculated

            in the earlier steps (2+ behind) -- only the Previous Cell could be addressed.

             

            Note that it is fundamentally different from referencing other Table calculations --

            via LOOKUP() or WINDOW_() functions. The latter calculates in a single pass (as usual).

             

            To make Tableau do what you're wanted it to do -- a kind of 'smoothing' calculation

            like the one in the attached sample Excel file -- would require a substantial effort (and trickery).

             

            The idea is to use PREVIOUS_VALUE() to calculate from (and store to) a Stack of values.

            So it would be a 'Feeder' calculation (doing all heavy-lifting in a single pass)

            and a 'Resulting' one (extracting a resulting value from the higher digits of the 'Feeder').

             

            The technique described above has been applied previously (shameless plug of yours truly :-)

             

            Re: Custom Moving Calculation?

             

            Your case is a bit complicated than the one linked above,

            but a general approach could be applied to it, too.

             

            Please find the attached as an example of the 'solution'.

            The Feeder calc would look rather complicated (and poor-documented).

            I apologise for that.

             

            The 'solution' itself doesn't scale well (if at all),

            though i've tried to write the calcs an uniform as possible

            for others to be able to expand the logic to their requirements

            (more calc depth and custom 'smoothing' logic).

             

            Hope you couldn't be trapped in this 'corner' of Tableau for too long :-)

            Hope you continue using this great tool where it bests and shines.

             

             

            Yours,

            Yuri

             

            PS  Generally, a recursion is a corner case in Tableau.

            Recursion could be easily done in Excel (which is a Cell-based iterative calc engine)

            or in any functional programming language (via iterating upon a Cursor).

             

            But it's hard (if possible) in plain SQL (which Tableau has many in common with).

            In some dialects of SQL the recursion is implemented via language extensions

            (Common Table Expressions) or via special (non-standard SQL) programming (PL etc).