Running sum based on date comparison
Steven Devisch Oct 28, 2012 1:28 PMHi,
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
InsertDate  Amount  ActionDate  Action 
1/1/2012  $ 1,000  1/3/2012  Closed 
1/2/2012  $ 1,100  1/3/2012  Validation 
1/3/2012  $ 1,210  1/5/2012  Closed 
1/4/2012  $ 1,331  1/5/2012  Validation 
1/4/2012  $ 1,464  1/8/2012  Validation 
1/5/2012  $ 1,611  1/8/2012  Closed 
1/6/2012  $ 1,772  1/8/2012  Closed 
1/7/2012  $ 1,949  1/8/2012  Closed 
1/9/2012  $ 2,144  1/8/2012  Closed 
Calculation
Date  OpenedAmount  ClosedAmount  RunningOpened  RunningClosed  RunningNet 
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

RunningOpenClosed.xlsx 11.1 KB

RunningOpenClosed.twbx.zip 29.8 KB