3 Replies Latest reply on Mar 17, 2019 8:40 PM by Arvind Kumar

# Conditional moving average

Dear all,

I have a table similar to the following:

 1 B C D 2 Date Profit Profit 2 3 Mar-17 12,123 12,123 4 Apr-17 34,432 34,432 5 May-17 34,432 34,432 6 Jun-17 12,332 12,332 7 Jul-17 34,342 34,342 8 Aug-17 65,656 65,656 9 Sep-17 - 37,443 10 Oct-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

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

1 of 1 people found this helpful
• ###### 2. Re: Conditional moving average

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?

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).

1 of 1 people found this helpful
• ###### 3. Re: Conditional moving average

Hi Fc,

Did you get this working?