4 Replies Latest reply on Oct 3, 2013 10:44 AM by adam.medcalf

    Running Sum based on full extract

    Michel Caissie

      Hi,

       

      I am relatively new to Tableau and i have a question regarding Table Calculations.

       

      My question is , is it possible to make some calculations based on all the data in the extracts, independently

      of the rows filtered?

       

      My goal is to display the running sum of measures and i want the calculation to start from the first row  in the extract and

      not the first row in the Window.

       

      Attached is an example using the SuperStore sample. On sheet1 i display the number of orders for the complete time range

      with the Cumulative values starting in 2009.

      On sheet2 i display  the number of orders for 2012 only  but i would like the Cumulative Number of Orders to be the same as on sheet1.

       

      In my real world i have to produce a daily report displaying  daily stats with some cumulative values.

      In mySql , i have to do a subquery to count the cumulative values

         

      select

      e.time as 'Time filter in Tableau'

      e.value1,

       

      (select count(event.value1)

      from event

      where event.time <= e.time

      ) as 'cumulative value1'

       

      from

      event e

       

       

      The problem is that if i have a zillion event per day, i have to calculate the same value a zillion time

      so i can have it available on every row, and this really kills the query performance.

       

      I guess this should be a common problem ,  i am open to any suggestions to accomplish this whether in Tableau

      or MySQL.

       

      thanks.

       

      Michel

        • 1. Re: Running Sum based on full extract
          Joshua Milligan

          Michel,

           

          The reason the filter in your example doesn't work is that it is applied at the data source level (in this case, the extract -- but it's true for any source).  The table calculation is then applied once the data is returned to Tableau's data engine.  At that point, only data for 2012 is available for the table calc.

           

          The good news is that you can achieve the answer you are looking for.  If you use a table calc as the filter, then it will be applied after the data returns to the Tableau data engine.  Your Window_Sum will be calculated for all the data, but then the table calc filter will be applied afterwards.  That will give you the answer you want.

           

          The calculated field to use as a filter looks like this:

           

          LOOKUP(MIN([Order Date]), 0)

           

          I've attached a workbook so you can see it in action.

           

          Joshua

          • 2. Re: Running Sum based on full extract
            Michel Caissie

            Thanks Joshua,

             

            It's exactly what i needed.

             

            So if i stand correctly,  a  ''simple calculated field''  will immediately add a a column in the Tableau Data Engine (view data) and the results are available for every row filtered ,  but  a calculation including a Table function (Lookup, Running, Window)  will be calculated only on the window, meaning on the row already filtered ... with the exception that if the Table calculation is applied on a filter, then the calculation will be processed before the rows gets filtered and displayed.

            • 3. Re: Running Sum based on full extract
              Joshua Milligan

              Michel,

               

              That's close.  Whenever you apply a dimension, measure, or Row Level calculated field as a filter, then a query is generated to retrieve only the underlying data that meets the query condition.

               

              Table calculations are done after that data returns.  So using a Table Calculation as a filter allows all the data to be returned to the Tableau data engine first.  Then other table calculations will be processed.  Finally, the table calculation filter will be applied.

               

              Joshua

              • 4. Re: Re: Running Sum based on full extract
                adam.medcalf

                Hi Joshua,

                 

                I apologise that this is an old thread, but in case it is OK to "reopen", I wanted to say thanks for the example as I was having the exact same problem as Michel.

                 

                I downloaded your example twbx and it works great, but I found I couldn't implement it in my own file for some reason. Stranger yet, just within your example file, if I remove the "year filter" and then try adding it back in immediately I can't make it work!

                 

                When I add it back in, instead of the nice 2009 2010, 2011, 2012 dropdown, I get instead the "relative date range" picker. And no matter what I select in there, the filter pill goes red and has no effect.

                 

                Capture.GIF.gif

                 

                I am sure I must be missing a trick as obviously it worked perfectly before I removed and added it back. Was wondering if you had any suggestions as to what that would be? Hopefully that would let me implement the scheme in my workbook.

                 

                Thanks a lot,

                 

                Adam

                 

                PS: If it is better for me to open a new question on this one, hopefully a friendly forum admin can tell me - wasn't sure the best approach when it relates to a file already in an answer!