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
[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
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.)
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.
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.
Basically a table calculation that uses the formula provided
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.
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.
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).
apples.twbx 23.7 KB
Asking the idea requires a specific wording. Tableau has moving averages, rolling 12-month (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.
Custom Moving Calculations or Advanced Moving Calculations
1 of 1 people found this helpful
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.)
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 Year-2, Year-1 and current Year, respectively.
Hope it could be of some help (if any).
apples_YF.twbx 105.9 KB
“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.