
1. Re: Custom Moving Calculation?
Joe Oppelt Feb 9, 2017 1:49 PM (in response to Joshua Gearheart)(Note to self: V9.3 here)
Your calc worked. See [Calculation1] in the attached,

apples A.twbx 21.8 KB


2. Re: Custom Moving Calculation?
Joshua Gearheart Feb 13, 2017 7:59 AM (in response to Joe Oppelt)Joe 
Thanks for the reply. The calc I shared doesn't work for me. I did a poor job explaining because this is hard to word, hopefully this provides some clarity:
In my workbook the Apples (calc) formula is straightforward, i.e:
IF [Apples 2015 (p)]>0
AND
[FY]=2015
THEN
[Apples 2015 (p)]
and so on...
What I wish to do is create another calculated field that uses the values in Apples (calc), let's call it "Apples 2" and compute this math:
(LOOKUP(ZN(SUM([Apples 2])), 2) + (LOOKUP(SUM(([Apples 2])), 1)*2))/3
Logically, I want to use Apples 2 for this calculation because the math should be a moving calculation. Using Apples 2, however, creates a circular reference which Tableau doesn't like. If I use Apples (calc) in place of Apples 2 there is an illusion that the calculated field works, but it's not the result I want.
Here's an example of the result I'm looking for:
Using the parameters a user enters 500 for 2015 and 500 for 2016. These entries 'overwrite' values in Apples (calc). Apples 2 displays apples for 2017, 2018 and 2019 as 500 because (500+(500*2)) / 3 = 500 or (Apples 2015+(Apples 2016 *2)) / 3 = 500

3. Re: Custom Moving Calculation?
Joe Oppelt Feb 13, 2017 8:15 AM (in response to Joshua Gearheart)Right now, Calculation1 DOES show 500 for 2017.
The offsets in LOOKUP go to specific locations. From 2017, an offset of 2 goes to 2015 and collects the value 500. And from 2017, the offset of 1 goes to 2016 and also collects 500, and you get the results you expect. (Calculation 1 is using [Apples (calc)] still.
But in 2018, the offset of 1 is getting 2017's data, so you're not getting 500 any more.
Let me ask about 2015. Right now Calculation1 is going back 2 and 1 years respectively. And I'm guessing it is giving you the value you want. Likewise 2016, though part of the calc is using the 500 override value stored in 2015.
What I don't understand is why you expect 2018 to arrive at 500 when no override value was shoved into 2017. (Likewise 2019.)

4. Re: Custom Moving Calculation?
Joe Oppelt Feb 13, 2017 8:16 AM (in response to Joe Oppelt)Attached has the two override values your example used.

apples B.twbx 21.8 KB


5. Re: Custom Moving Calculation?
Joshua Gearheart Feb 13, 2017 8:34 AM (in response to Joe Oppelt)Joe,
Thanks again.
Your question leads to my point about the circular reference. I want the calculation to be moving. Meaning, when 2015 and 2016 both become 500 after input from the parameters so does 2017 because of the calculation. Since 2016 and 2017 are now 500 so should be 2018 because it uses the same formula to receive its value.
I'm not seeing any of your attachments.

6. Re: Custom Moving Calculation?
Joe Oppelt Feb 13, 2017 8:45 AM (in response to Joshua Gearheart)I am guessing, then, that once any year gets set by a parameter, all subsequent years need to adjust accordingly.
You want to look at the PREVIOUS_VALUE function. This is the place where tableau gives us recursion. But one problem I see is that PREVIOUS_VALUE will only look back one cell. (Thus the name, "previous".)
Maybe you would have to have some parallel calc that uses previous value, and stores for a given year the previous year's value. (Just thinking out loud here...) Your solution probably lies with PREVIOUS_VALUE though. One way or another.

7. Re: Custom Moving Calculation?
Joshua Gearheart Feb 13, 2017 8:46 AM (in response to Joshua Gearheart)Basically a table calculation that uses the formula provided

8. Re: Custom Moving Calculation?
Joe Oppelt Feb 13, 2017 11:47 AM (in response to Joshua Gearheart)Joshua  I've been hacking around with PREVIOUS_VALUE and keep getting oh, so close. But I can't escape recursion trying to reach back TWO cells.
I started a new thread to get some fresh perspective.Is there a way to create something like (PREVIOUS_VALUE(0),2) ?
I'm not convinced that there is not a way to do this without recursion. I would like to see what happens in that new thread before we say we have to throw in the towel.

9. Re: Custom Moving Calculation?
Joshua Gearheart Feb 13, 2017 12:10 PM (in response to Joe Oppelt)Joe 
I found a solution in the attached. Essentially created a field for each year, calling upon the previous using LOOKUP, then grooming all years at the end in an IF statement using Apples (final). Also changed the results of the Apples (calc) to be ATTR(Apples) instead of just Apples.
Probably not the best way to do this, but it works. My request from Tableau would be to allow custom Moving Calculations (I'll scroll through Ideas to see if this exists).
Thanks again.

apples.twbx 23.7 KB


10. Re: Custom Moving Calculation?
Joe Oppelt Feb 13, 2017 12:46 PM (in response to Joshua Gearheart)Asking the idea requires a specific wording. Tableau has moving averages, rolling 12month (or any duration) calcs, etc. Asking for "Moving Calculations" isn't going to go far.
In your case what you really need is a PREVIOUS_VALUE that lets you specify how "previous" you need to jump back. 
11. Re: Custom Moving Calculation?
Joshua Gearheart Feb 13, 2017 12:52 PM (in response to Joe Oppelt)Custom Moving Calculations or Advanced Moving Calculations

12. Re: Custom Moving Calculation?
Jonathan Drummey Feb 23, 2017 3:20 PM (in response to Joshua Gearheart)1 of 1 people found this helpfulHi Joshua,
I'm glad you were able to get to a solution on this! In general my recommendation for this kind of nested looping problem is to use the R or (as of v10.1) Python integrations, we can send data to R & Python and write as complex a logic as we want and return the results to Tableau.
In my experience this is a pretty rare problem (at least on the forums), I only see it once per year or so. If you're going to post an Idea I'd suggest naming it something like "nesting PREVIOUS_VALUE() or "expanded recursion" or "loop constructs" (or all of the above) since those are more specific than "moving calculations". (An example of why Joe and I are being picky about wording: About once per year or so someone comes along claiming they've got a much simpler solution for the moving count distinct problem in Tableau and it always turns out that they've built a moving calculation, but it's not solving the specific moving count distinct issue.)
Jonathan

13. Re: Custom Moving Calculation?
Yuriy Fal Feb 24, 2017 10:10 AM (in response to Jonathan Drummey)Hi friends,
As a pure exercise I'd like to munch the idea
of a "Feeder" calculation (Jonathan's term)
holding more than one value at once.
It could be either a STRING concatenate
or an INTEGER (compound) value.
I use the latter in the attached.
With such a Feeder calc in place
one could use a PREVIOUS_VALUE()
to extract the parts, calculate the result
and combine it with the other parts
back into the compound value.
So one could have the current result
together with the previous one(s) in a single pass.
Please find the attached as an example
(on the Sheet 7 of course :).
I've been trying to implement
a "smoothing" Moving calculation
with the weights of 1,2 & 4 (total 7)
for the Year2, Year1 and current Year, respectively.
Hope it could be of some help (if any).
Yours,
Yuri

apples_YF.twbx 105.9 KB


14. Re: Custom Moving Calculation?
Jonathan Drummey Feb 24, 2017 10:13 AM (in response to Yuriy Fal)“pure exercise” == “Yuriy being a genius”
I’d forgotten about the overloading a field technique, Noah Salvaterra first introduced it to me in this context with his work on fractals in Tableau.
Jonathan