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