8 Replies Latest reply on Oct 7, 2016 7:32 AM by Bernard Chang

    Table Calc Dynamic Window

    Bernard Chang

      Hello all

       

      I am stuck with this problem for a long time and I hope someone can shed me some lights..

       

      In the following example, I want to calculate the sum of Values where the Date of the item is within certain number of days (parameter input p) of the current row.  I am on Tableau 9.2, and I couldn't manage to find any table calc functions or any other ways that can solve this problem.

       

       

      Any thoughts will be helpful.

       

      Thanks

      Bernard

        • 1. Re: Table Calc Dynamic Window
          Joe Oppelt

          You can do things like this:

           

           

          WINDOW_SUM( IF whatever-condition-you-want-here THEN SUM([Value]) END )

           

          You may have to edit the table calc to cycle through specific dimensions, or to do TABLE(down) or TABLE(across), and this will be very specific to the layout on the sheet where you are using the table calc.

           

          The best I can do right now is give you this very general answer.  In short, you CAN do this.  For more specifics, it will require a packaged workbook so that the response can be tailored to the specifics of your sheet.

          • 2. Re: Table Calc Dynamic Window
            Bernard Chang

            Thanks, Joe.  It is definitely helpful.  I think I am almost there..

            However, I couldn't set the "Relative to" to iterate through the table therefore all rows are showing the same output.  I must have missed something in the formula.  I am attaching the workbook hopefully you can help.

             

            Thanks

            Bernard

            • 3. Re: Table Calc Dynamic Window
              Joe Oppelt

              (Note to self:  V9.2 workbook)

               

              "Relative to" is caused by the LOOKUP.  That usually has some sort of offset in there to tell Tableau what value to look up.

               

              In the attached I put an offset of -1.  (Tells Tableau to look at the previous value.)  Now the "Relative to" is gone.  I don't know what offset you really need it to grab, but that' how you do it.

              • 4. Re: Table Calc Dynamic Window
                Bernard Chang

                I think the problem is that I cannot set an offset for the lookup function because I need it to iterate through all rows in the partition.

                 

                An arbitrary offset gives me same output for all rows.

                • 5. Re: Table Calc Dynamic Window
                  Joe Oppelt

                  What are you looking to do there?  There's probably a better way to do it.

                  • 6. Re: Table Calc Dynamic Window
                    Bernard Chang

                    I just want to get the desired output for each row as in my very first post. 

                    The solution to this problem will help me in many complex applications

                    • 7. Re: Table Calc Dynamic Window
                      Joe Oppelt

                      OK, now I'm beginning to understand the issue here.


                      So a table calc arrives at ONE number for the set of data being examined.  (Either:  For the whole table, give me one number, or, for a certain dimension value, give me one number for all the rows within that value.)

                       

                      What you are looking to achieve is a sort of running table calc.

                       

                      WINDOW_SUM also has a feature that lets you specify a starting position (relative to the row you are sitting on) and an end position (relative to the current row.)  So if you had continuous dates and the rows were treated in date order, you could do a window_sum(sum([Value]), ([P]*-1)+1, [P]-1).  This would look backward p_many days, and forward p_many days, and sum that up.

                       

                      The way you are running this data, you are essentially saying this:  Look at every row in the data.  If the date is within p_many days of "this row", then add the [Value].  Do this for all rows.

                       

                      First, that's going to be a VERY expensive operation if you end up with a lot of rows.  And this approach is very much like the problem we have with computing a "Rolling COUNTD".  (For instance, give me a COUNTD of all the userIDs in each 12-month period, month over month.)


                      There are some lengthy discussions out there on this, and it's complicated to do that in Tableau.


                      Here is one such thread:

                       

                       

                      How to count distinct users on a running period

                       

                      A key statement from that thread is this:  "I think this is a really good example of a type of problem which is unreasonably difficult to do with table calculations. The requirement is very easy to define, and the problem can be solved very easily in SQL."

                       

                      It is a long thread with history going back to 2013 (version 7.0 of tableau!) and there is some progression that incorporates V9.x LOD capabilities.  You especially want to pay attention to the stuff by Joe Mako in that thread.  Back in the 8.x days I made this work on a small set of data, but it was impractical for our actual full set of data.  We went the SQL route.

                       

                      I do Rolling-12 window sums all over the place in tableau, but I run the data along the date dimension and just have to do window_sum(sum[Sales]),-11,0).  If you could get your data running along a date dimension, you could do the same sort of window_sum looking back [P]-1 and looking forward [P]-1.

                      • 8. Re: Table Calc Dynamic Window
                        Bernard Chang

                        Thanks again, Joe.   Although I did not get a solution, I have confirmed that this is a very difficult problem for Tableau.  I don't think domain completion/padding will work for my case.  I might just have to sleep on it a little more or just use  another tool.  I will update this post if I have a solution with Tableau.