I have a data set that contains begin and termination dates I want to sum the column seatcount within.
This is some representative data:
BeginDate TermDate SeatCount
| 12/1/2008 | 3/31/2009 | 2 |
| 7/16/2014 | 1 |
| 10/23/2012 | 12/31/2012 | 0 |
| 6/10/2008 | 7 |
| 3/18/2013 | 10 |
| 1/1/2001 | 3/1/2010 | 10 |
| 8/13/2013 | 3/31/2014 | 10 |
| 6/26/2014 | 3 |
| 3/1/2008 | 6 |
| 12/4/2013 | 1 |
| 8/7/2013 | 2 |
| 11/7/2013 | 1 |
What I have then is a pivot table with a single dimension MonthEnd(BeginDate). The expression i want is based on the following:
sum(if(TermDate<=BeginDate, 0, SeatCount)).DateDate
| =MonthEnd(BeginDate) | sum(if(TermDate<=BeginDate, 0, SeatCount)) |
| 8/31/2014 | 10 |
| 7/31/2014 | 293 |
| 6/30/2014 | 231 |
| 5/31/2014 | 215 |
| 4/30/2014 | 354 |
| 3/31/2014 | 321 |
| 2/28/2014 | 158 |
| 1/31/2014 | 296 |
| 12/31/2013 | 157 |
| 11/30/2013 | 203 |
| 10/31/2013 | 151 |
| 9/30/2013 | 270 |
| 8/31/2013 | 148 |
| 7/31/2013 | 188 |
| 6/30/2013 | 188 |
| 5/31/2013 | 186 |
| 4/30/2013 | 264 |
| 3/31/2013 | 178 |
What I want is the accumulated seatcount minus the seatcount that is terminated. My thought is I should be getting a number that is increasing by date, but that is not happening. Is this a recursion issue?
