1 Reply Latest reply on Apr 11, 2012 1:58 PM by Alex Kerin

    A subtraction fucntion based on date

      Hello,

       

      I have a data set with a variety of HR dimensions and measures.  One of the dimensions is projected retirement date (Service Completion Date), I would like to show what the size of the workforce would look like assuming everyone who retires on the projected date actually retires.  I can create a viz that displays the cumulative retirements, (Ret Date & Count of Ret Date), but I'm stumped on how I create a "remaining workforce" viz.  I've tried total sum (of records)- count of service, window sum - count of service, and sum - count of service, and I can't seem to figure it out.  I've attached for your reference.

       

      Thanks a bunch.

        • 1. Re: A subtraction fucntion based on date
          Alex Kerin

          I thought this was going to need custom_sql to decrement the employees, but this seems to work (please check)

           

          previous_value(total(countd([EmpID])))-countd([EmpID])

           

          This means on the first row, use the total number of employees (by the way, you do have null IDs and some null end service dates), then for each row after that (in this case year) take away the number that end that year away from the row above.