4 Replies Latest reply on Oct 11, 2019 3:31 AM by selva kumar

    MTD, QTD, YTD Calculations?

    Veera Veeru

      Wanted to understand on how to calculate MTD, QTD and YTD with historical data.

       

      Please help me in getting below data using functions.

          

      MonthSalesPrior MonthMTDQTDYTDFull Year
      01/01/20188135
      01/02/20188996
      01/03/20188423
      01/04/20188712
      01/05/20189150
      01/06/20188107
      01/07/20189145
      01/08/20189676
      01/09/20189474
      01/10/20189801
      01/11/20189087
      01/12/20188513
      01/01/20198852
      01/02/20198608
      01/03/20199615
        • 1. Re: MTD, QTD, YTD Calculations?
          Manjula Muppavaram

          Hi Veeru,

           

          please see below calculation, it might hep you. it is is useful mark as useful.

           

          MTD: if datediff('month',[Date],today()=0 and [Date]<today() then [sales] end

           

          QTD: if datediff('quarter',[Date],today()=0 and [Date]<today() then [sales] end

           

          YTD: if datediff('year',[Date],today()=0 and [Date]<today() then [sales] end

           

           

          regards,

          Manjula

          • 2. Re: MTD, QTD, YTD Calculations?
            Veera Veeru

            Hi Manjula,

             

            Sorry, this is not what I was looking for. I don't want to use today function as I have to use historical data for my calculations.

             

            Thanks

            • 3. Re: MTD, QTD, YTD Calculations?
              Diego Parker

              Hi Veera,

               

              The easiest way to do it is by using table calculations. The calculations look very similar but make sure to look at how the table calculation is configured. Also, keep in mind I hid the headers for the different date parts but for the Table Calcs to work it is necessary that they are in the view.

               

              Please find the example attached:

               

              Table Calc.PNG

               

              Obviously, because your data is at monthly level, the MTD will have the same result as the sales field.

               

              If you have any questions, please do not hesitate on asking.

               

              If this helps you or answers your question, please mark it as helpful and correct so other users can refer to it.

               

              Thanks,

              Diego

              1 of 1 people found this helpful
              • 4. Re: MTD, QTD, YTD Calculations?
                selva kumar

                Hi, Veera,

                 

                1. IF [parameter_name]='YTD' AND [Date]>= DATETRUNC('year',today())
                2. THEN [Date]
                3. ELSEIF [parameter_name]='QTD' AND [Date]>= DATETRUNC('quarter',today())
                4. THEN [Date]
                5. ELSEIF [parameter_name]='MTD' AND [Date]>= DATETRUNC('month',today())
                6. THEN [Date]
                7. END

                thanks ,

                selva

                1 of 1 people found this helpful