2 Replies Latest reply on Oct 8, 2018 6:36 AM by Jim Dehner

    what is this calculation doing

    Imran Azam

      HI Guys i have the calculation field below which i dont fully understand what its is doing

       

      IF DATEPART('year',DATEADD('month',-6,[Everyday]))  = DATEPART('year',DATEADD('month',-6,[endatemax]))

              THEN "LAPSED"

      ELSEIF DATEPART('year',DATEADD('month',-6,[Everyday])) = DATEPART('year',DATEADD('month',-6,[startatemin]))

              then "NEW"

      ELSEIF DATEPART('year',DATEADD('month',-6,[Everyday])) > DATEPART('year',DATEADD('month',-6,[startatemin]))

         and DATEPART('year',DATEADD('month',-6,[Everyday])) <  DATEPART('year',DATEADD('month',-6,[endatemax]))

              then "REPEAT"

      END

       

      can anyone explain to me in steps what this is doing?

       

      thank you

        • 1. Re: what is this calculation doing
          meenu choudhary

          Hi,

           

           

          DATEADD('month',-6,[Everyday])   =  subtracting 6 month from [Everyday]

          DATEADD('month',-6,[endatemax]) = subtracting 6 month from [endatemax]

          DATEADD('month',-6,[startatemin]) = subtracting 6 month from [startatemin]

           

           

           

          IF DATEPART('year',DATEADD('month',-6,[Everyday]))  = DATEPART('year',DATEADD('month',-6,[endatemax]))    THEN "LAPSED"

           

          Take the year part of both the dates , if same than "LAPSED"

           

          ELSEIF DATEPART('year',DATEADD('month',-6,[Everyday])) = DATEPART('year',DATEADD('month',-6,[startatemin]))   then "NEW"

           

          Take the year part of both the dates , if same than "NEW"

           

          ELSEIF DATEPART('year',DATEADD('month',-6,[Everyday])) > DATEPART('year',DATEADD('month',-6,[startatemin]))

             and DATEPART('year',DATEADD('month',-6,[Everyday])) <  DATEPART('year',DATEADD('month',-6,[endatemax]))         then "REPEAT"

           

           

          If Year of  new Everyday is between year of startatemin and Year of  endatemax then "REPEAT"

           

          • 2. Re: what is this calculation doing
            Jim Dehner

            good morning

            need a little more detail here

            you have 2 fields startdaemin and enddatemax - that are not defined - I assume (always a dangerous thing) that they are lod's that find the min or max of fields start and end dates

             

            so what ht calculation does is it looks at each record in the viz (each record has a field "everyday " and starting at the top

              everyday is being adjusted 6 months back - probably to create a fiscal year  the first clause checks the fiscal year of everyday against end date and if they are equal it returns Lapsed

            the second clause (if the first is clause is not true) will return 'new' if the fiscal year of everyday is equal to that of min start date

            if both of those are false then if the fiscal year for each record is between the min start date and max end date it reurns "Repeat'

             

            if none of the above are true it returns a null

             

            Jim

            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.