2 Replies Latest reply on Apr 17, 2016 2:03 PM by Yuriy Fal

    Complex Calculations

    niranjan.solase.0

      I am trying to solve following issue here -

       

      PFA workbook in order to refer -

       

      I have following data with me  -  I need to add another 2 new calculative field which will Select them Based following rules. Lets call that new Id as New_Type and New Date

       

      If Min date for each ID has Type 'b' then New_Type should be Business and New date should be the Min date of that Type.

      else if Min date (First) for unique ID is of Type 'a' then find out next minimum Date within 48 hours with reference to first Min date for that ID and check if its 'b' , If it is then New_Type should be Business and New date should be the Min date of that Type.

       

      --Note -> In this case suppose we have many Type 'b' for one ID with different time within 48 hours of First Min Date then select the Min of all that and select its time as new_date and new_type as Business.

       

      So in this case -

       

      For ID == 1

      Min date is 12/1/2015 1:00 but as it is of type a we need to check if other dates for that ID within 48 hours referring to 12/1/2015 1:00 is present with type b.

       

      In this scenario we have 2 'b' within 48 hours of min date which are - 12/1/2015 4:00 and 12/1/2015 6:00. But as 12/1/2015 4:00 is Min among  both we should select new_date as 12/1/2015 4:00 and new type as Business as we got Type 'b' here.

        

      For ID == 2

      as Min date is 4/4/2015 1:00 but with Type 'a' we should select if other dates for that ID within 48 hours referring to  4/4/2015 1:00 is present with type b.

       

      again here we have only 1  dates within 48 hours referring to first Min date along with Type 'b' we should select  4/5/2015 10:00 as New_date.

       

      For ID ==3

       

      as we have Min date with type b so its straight forward and New_date here will be 4/6/2015 1:00.

       

      For id ==4

       

      as we have Min date but with Type 'a' we should search for next Min date for that Id with Type B within 48 hours. As we dont have the same we should go ahead with the new_type as 'Non-Business'

       

       

       

      DateTypeID
      12/1/2015 1:00a1
      12/2/2015 0:00a1
      12/1/2015 4:00b1
      12/2/2015 23:00a1
      12/1/2015 6:00b1
      4/4/2015 1:00a2
      4/5/2015 10:00b2
      4/6/2015 1:00b2
      4/6/2015 1:00b3
      6/6/2015 5:00a4
      8/6/2015 1:00a4