
1. Re: Conditional moving average
Patrick A Van Der Hyde Sep 20, 2018 1:40 PM (in response to Francesco Cometa)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 Sep 30, 2018 11:46 PM (in response to Francesco Cometa)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 (multipass) 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 heavylifting 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 poordocumented).
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 Cellbased 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 (nonstandard SQL) programming (PL etc).

Shmoozy_v10.2_YF.twbx 2.4 MB

Shmoozy.xlsx 11.1 KB
