0 Replies Latest reply: Aug 27, 2014 10:17 AM by Glenn Melton RSS

    us map control

      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/20083/31/20092
      7/16/20141
      10/23/201212/31/20120
      6/10/20087
      3/18/201310
      1/1/20013/1/201010
      8/13/20133/31/201410
      6/26/20143
      3/1/20086
      12/4/20131
      8/7/20132
      11/7/20131

       

      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/201410
      7/31/2014293
      6/30/2014231
      5/31/2014215
      4/30/2014354
      3/31/2014321
      2/28/2014158
      1/31/2014296
      12/31/2013157
      11/30/2013203
      10/31/2013151
      9/30/2013270
      8/31/2013148
      7/31/2013188
      6/30/2013188
      5/31/2013186
      4/30/2013264
      3/31/2013178

       

       

      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?