1 Reply Latest reply on Oct 29, 2012 7:39 AM by Jonathan Drummey

    Running sum based on date comparison

    Steven Devisch
    Steven Devisch Oct 28, 2012 1:28 PM

      Hi,

       

      I'm looking to calculate a net running sum (inserted - closed). My data however is not organized in a opened / closed list of rows as can be found in the top 10 calculations section (see Data section below). It is organized in columns, multiple inserts can happen on the same day and "closed" is not the only action, although it is the only taken into account in the "net calculation" (pls see the "Calculation tab in the enclosed excel".

       

      I'm assuming I need some kind of a window_sum.  I thought this would do the trick, but it does not:

       

      WINDOW_SUM(SUM([Amount]),-INDEX(),0)-IIF(ATTR([ActionDate]) > window_max(ATTR([InsertDate]),-INDEX(),0),WINDOW_SUM(SUM([Closed]),-INDEX(),0),0)

       

      I've included what I want to do both in excel and a in tableau. The "Calculated" tab in excel and tableau show the end result I am looking for. However, I would like to produce the graphs from the excel "Data" tab, not from the "Calculation" tab.

       

      DATA

      InsertDateAmountActionDateAction
      1/1/2012 $  1,000 1/3/2012Closed
      1/2/2012 $  1,100 1/3/2012Validation
      1/3/2012 $  1,210 1/5/2012Closed
      1/4/2012 $  1,331 1/5/2012Validation
      1/4/2012 $  1,464 1/8/2012Validation
      1/5/2012 $  1,611 1/8/2012Closed
      1/6/2012 $  1,772 1/8/2012Closed
      1/7/2012 $  1,949 1/8/2012Closed
      1/9/2012 $  2,144 1/8/2012Closed

       

      Calculation

      DateOpenedAmountClosedAmountRunningOpenedRunningClosedRunningNet
      1/1/2012 $   1,000 $                          -   $                1,000 $                          -   $                 1,000
      1/2/2012 $   1,100 $                          -   $                2,100 $                          -   $                 2,100
      1/3/2012 $   1,210 $                   1,000 $                3,310 $                   1,000 $                 2,310
      1/4/2012 $   2,795 $                          -   $                6,105 $                   1,000 $                 5,105
      1/5/2012 $   1,611 $                   1,210 $                7,716 $                   2,210 $                 5,506
      1/6/2012 $   1,772 $                          -   $                9,487 $                   2,210 $                 7,277
      1/7/2012 $   1,949 $                          -   $              11,436 $                   2,210 $                 9,226
      1/8/2012 $          -   $                   7,474 $              11,436 $                   9,684 $                 1,752
      1/9/2012 $   2,144 $                          -   $              13,579 $                   9,684 $                 3,895

       

      Thank you very much in advance for any help,

       

      Steven

        • 1. Re: Running sum based on date comparison
          Jonathan Drummey
          Jonathan Drummey Ambassador Oct 29, 2012 7:39 AM (in response to Steven Devisch)

          Hi Steven,

           

          There are a couple of ways to get the view you want. Either way, you will need to give Tableau more data to work with, since the view you outlined needs to plot the amount based on the InsertDate (for the Open), and the amount based on the ActionDate (for Close actions), where the InsertDate and ActionDate's don't line up. You can set up multiple calculations to present multiple measures in a view, like you did with the Closed measure, but you need to make sure every date that you have data for that you want to plot is represented in the Date dimension you are using.

           

          The first option is to use Custom SQL to take the original data as one set of rows for the Open position and InsertDate for the Date, and UNION that to a second query that gets the other positions and the ActionDate for the Date, with the same Amount for both queries. That gets us a data set with a row for every value to plot, and then Running Sum's can be created for each. Note that I added a ZN() around the running sum to make sure that the Net calc has values to work with.

           

          This view has one drawback. In the BasedOnData view, the green line for the Running Sum of Open goes from Jan 7 to Jan 9, when it should really be horizontal through Jan 8. You don't see that in the CustomSQL view because in the UNION'ed data source there is a row for Jan 8, if there were missing days in the union then you'd get diagonals.

           

          The second option that gets around this problem is to use a master table of dates and then left-join your data to that, twice (once for the Open and once for the Close position). This gets around the problem above. This could be done in Custom SQL, the query in the original data source, or in a Tableau data blend, I set up the latter. I'll often start out with something like this as I'm initially building a view, then when possible I'll move the work of doing the relating into the original data source.

           

          Jonathan