1 2 Previous Next 16 Replies Latest reply on Feb 27, 2017 8:44 AM by Joshua Gearheart

    Custom Moving Calculation?

    Joshua Gearheart

      I want to apply a custom moving calculation to my Apples (calc) in the attached workbook.  Ideally I could use the below formula, but I'm prevented by the inability to implement circular references.

       

      (LOOKUP(ZN(SUM([Apples (calc)])), -2) + (LOOKUP(ZN(SUM(Apples (calc))), -1)*2))/3

       

      Basically each value will take the second predecessor, add the first predecessor, multiply by two then divide by three. 

       

      Can I do this using a custom moving calculation?  Does that exist?  I only see some stock formulas like sum, average, max, min, etc.

       

      I've devised a way to do this using a calculated field that consists of 120 combinations (5*4*3*2*1) thrown into an if statement, but it seems like there should be an easier way. 

       

      I need the user to be able to overwrite values in the view with a parameter (see the Apples (calc) formula), and have the later values in the view be effected by the moving calculation.      

        • 1. Re: Custom Moving Calculation?
          Joe Oppelt

          (Note to self:  V9.3 here)

           

          Your calc worked.  See [Calculation1] in the attached,

          1 of 1 people found this helpful
          • 2. Re: Custom Moving Calculation?
            Joshua Gearheart

            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

              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

                Attached has the two override values your example used.

                • 5. Re: Custom Moving Calculation?
                  Joshua Gearheart

                  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

                    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

                      Basically a table calculation that uses the formula provided

                      • 8. Re: Custom Moving Calculation?
                        Joe Oppelt

                        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

                          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.

                          • 10. Re: Custom Moving Calculation?
                            Joe Oppelt

                            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.

                            • 11. Re: Custom Moving Calculation?
                              Joshua Gearheart

                              Custom Moving Calculations or Advanced Moving Calculations

                              • 12. Re: Custom Moving Calculation?
                                Jonathan Drummey

                                Hi 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

                                1 of 1 people found this helpful
                                • 13. Re: Custom Moving Calculation?
                                  Yuriy Fal

                                  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 Year-2, Year-1 and current Year, respectively.

                                   

                                  Hope it could be of some help (if any).

                                   

                                  Yours,

                                  Yuri

                                  • 14. Re: Custom Moving Calculation?
                                    Jonathan Drummey

                                    “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

                                    1 2 Previous Next