1 Reply Latest reply: May 8, 2013 5:42 PM by Paul Baenen RSS

    Calculate age, minus holding periods, in script

    David Koschei

      Hi everyone,

       

       

      IDStatusDate
      1Open1/1/2013
      1Changed from Open to Hold1/15/2013
      1Changed from Hold to Open2/1/2013
      1Changed from Open to Hold2/15/2013
      1Changed from Hold to Open3/1/2013
      1Changed from Hold to Closed3/15/2013

       

      What I want to do is Age(ID 1) = (3/15/2013 - 3/1/2013) + (2/15/2013 - 2/1/2013) + (1/15/2013 - 1/1/2013)

       

      Basically, the item should not age if it is in a "Hold" state.

       

      What's a good way of doing this?

       

      Thank you

        • Re: Calculate age, minus holding periods, in script
          Paul Baenen

          Can you just look at the previous status to determine the age? For example:

           

          Status:

          Load

                    if(ID=Previous(ID) and Previous(Status) <> 'Changed from Open to Hold', Date-Previous(Date)) as AgeDays,

                    *

                    ;

          LOAD * INLINE [

          ID, Status, Date

          1, Open, 1/1/2013

          1, Changed from Open to Hold, 1/15/2013

          1, Changed from Hold to Open, 2/1/2013

          1, Changed from Open to Hold, 2/15/2013

          1, Changed from Hold to Open, 3/1/2013

          1, Changed from Hold to Closed, 3/15/2013

          ];