7 Replies Latest reply on Oct 2, 2018 5:52 AM by Imran Azam

    Help with calculation please

    Imran Azam

      HI guys

       

      I am working on urgent work task trying to count the number of over new contracts, how many repeat and how many lapse.

       

      i have the data below ( i hjave attached my workbook too)

       

      account
      start date
      end date
      ab27/04/201726/05/2018
      ab19/07/201727/07/2018
      ac27/04/201526/03/2018
      ac27/06/201526/05/2018

       

       

      i have linked this to a separate sheet contain daily dates so i can get the dates between and also i can use to display the dates across , i have called this months

       

      • each account can have more that 1 contract but i want to count all the contracts  under an account as 1, using the min star date and the max end date, so in this case for account ab the contract start date would be  27/04/2017 and end date would be 27/07/2018
      • contract lapse on the max end date
      • repeat are the dates between the start and end date.

       

       

      so for account ab the contract starts 27/04/2017  s new should be shown at FY 2016-17 , and contract ends 27/07/2018 so it should show lapsed fy 18-19

       

      rest should be like below

       

      please note my FY are set to july-june

       

       

      statusFy-14-15Fy-15-16Fy-16-17Fy-17-18fy-18-19
      new11
      repeat111
      lapse11

       

       

      i am having major problems doing this my results do not seem to be right ( checked the attached workbook ) i don't know if it is my calcultion field?

       

      can anyone help me on this?

       

      if this is not clear i am happy to provide any other info.

       

      thank you guys

                                            

       

       

       

       

       

       

       

      27/04/2017