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?