2 Replies Latest reply on Mar 5, 2017 9:40 PM by sneha simha

    YTD

    sneha simha

      Dear All

       

                                  I want to calculate YTD for last years say from 2012 till date.Kindly help me with the formulae to achieve the same.However YTD for the current and Previous years have been achieved using the formulas

       

      if [ Date]>=DATE(STR(if MONTH(TODAY()-1)>3

      then YEAR(TODAY()-1) else YEAR(TODAY()-1)-1 end)+"-04-01")   (Current Year)

      and

      [ Date]<=TODAY()-1 then [sales]

      end

       

      if [ Date]>=DATE(STR(if MONTH(DATEADD('year',-1,TODAY()-1))>3

      then YEAR(DATEADD('year',-1,TODAY()-1))

      else YEAR(DATEADD('year',-1,TODAY()-1))-1 end)+"-04-01")                   (Previous Year)

      and

      [Date]<=DATEADD('year',-1,TODAY()-1) then [sales] end

       

       

      Regards

      Sneha simha

        • 1. Re: YTD
          Norbert Maijoor

          Hi Sneha,

           

          Find my approach as reference below and stored in attached workbook version 9.3 located in the original thread.

           

           

          1. YTD CY: if DATEDIFF('year',[date],TODAY())=0 and DATEDIFF('month',[date],TODAY())>=0 then [valeu] END

           

          2. YTD CY-1: if DATEDIFF('year',[date],TODAY())=1 and DATEDIFF('month',[date],TODAY())>=12 then [valeu] END

          3. YTD CY-2 :if DATEDIFF('year',[date],TODAY())=2 and DATEDIFF('month',[date],TODAY())>=24 then [valeu] END

          etc....


          Regards,Norbert

          • 2. Re: YTD
            sneha simha

            Hi Norbert

             

                                          M able to get only last 2 years sales,the same is shown below.

            I used the same formula given above and replaced the number from 24 to 36 and so on.

            Kindly Help me.

             

            Regards

            Sneha simha