9 Replies Latest reply: Jul 30, 2015 1:07 AM by Gautham Prasad RSS

    YTD Calculation

    Gautham Prasad

      Hi,

      I would like to calculate YTD in the following way.

       

      Ex: if my current selection is 2015 March, then the ytd calculation should be like below.

       

      sales for Mar 15+Sales for Feb 15+Sales for Jan15+sales for Jan14+Sales for Feb14+Sales for Mar14

       

      if I select jan 2015, then it should be

       

      sales for jan 15+sales for jan 14 alone

       

      How can I do this,

        • Re: YTD Calculation
          Sunny Talwar

          May be like this in your set analysis

           

          {<Date = {"$(='>=' & Date(YearStart(Max(Date))) & '<=' & Date(Max(Date)))"} + {"$(='>=' & Date(AddYears(YearStart(Max(Date)), -1)) & '<=' & Date(AddYears(Max(Date), -1)))"}>}

            • Re: YTD Calculation
              Gautham Prasad

              Thanks sunny for the reply.

               

              Small correction: if my current selection is 2015 then the formula should be like

              (sales for Mar 15+Sales for Feb 15)/2+(Sales for Feb 15+Sales for Jan15)/2+(Sales of Dec15+Sales for Jan15)/2

              (sales for Mar 14+Sales for Feb 14)/2+(Sales for Feb 14+Sales for Jan14)/2+(Sales of Dec14+Sales for Jan14)/2

               

              in this case, how can I do?

                • Re: YTD Calculation
                  Gautham Prasad

                  stalwar1 Can you help?

                  • Re: YTD Calculation
                    Sunny Talwar

                    May be like this:


                    (sales for Mar 15+Sales for Feb 15)/2

                    (Sum({<Date = {"$(='>=' & Date(MonthStart(Max(Date))) & '<=' & Date(MonthEnd(Max(Date))))"} Sales) +

                    Sum({<Date = {"$(='>=' & Date(MonthStart(AddMonths(Max(Date), -1))) & '<=' & Date(MonthEnd(AddMonths(Max(Date), -1))))"} Sales))/2


                    (Sales for Feb 15+Sales for Jan15)/2

                    (Sum({<Date = {"$(='>=' & Date(MonthStart(AddMonths(Max(Date), -1))) & '<=' &Date(MonthEnd(AddMonths(Max(Date), -1))))"} Sales) +

                    Sum({<Date = {"$(='>=' & Date(MonthStart(AddMonths(Max(Date), -2))) & '<=' &Date(MonthEnd(AddMonths(Max(Date), -2))))"} Sales))/2

                     

                    (Sales of Dec14+Sales for Jan15)/2

                    (Sum({<Date = {"$(='>=' & Date(MonthStart(AddMonths(Max(Date), -2))) & '<=' &Date(MonthEnd(AddMonths(Max(Date), -2))))"} Sales) +

                    Sum({<Date = {"$(='>=' & Date(MonthStart(AddMonths(Max(Date), -3))) & '<=' &Date(MonthEnd(AddMonths(Max(Date), -3))))"} Sales))/2

                     

                    See if the above helps.

                     

                    Best,

                    Sunny

                      • Re: YTD Calculation
                        Sunny Talwar

                        (sales for Mar 14+Sales for Feb 14)/2

                        (Sum({<Date = {"$(='>=' & Date(MonthStart(AddMonths(Max(Date), -12))) & '<=' &Date(MonthEnd(AddMonths(Max(Date), -12))))"} Sales) +

                        Sum({<Date = {"$(='>=' & Date(MonthStart(AddMonths(Max(Date), -13))) & '<=' &Date(MonthEnd(AddMonths(Max(Date), -13))))"} Sales))/2


                        (Sales for Feb 14+Sales for Jan14)/2

                        (Sum({<Date = {"$(='>=' & Date(MonthStart(AddMonths(Max(Date), -13))) & '<=' &Date(MonthEnd(AddMonths(Max(Date), -13))))"} Sales) +

                        Sum({<Date = {"$(='>=' & Date(MonthStart(AddMonths(Max(Date), -14))) & '<=' &Date(MonthEnd(AddMonths(Max(Date), -14))))"} Sales))/2

                         

                        (Sales of Dec13+Sales for Jan14)/2

                        (Sum({<Date = {"$(='>=' & Date(MonthStart(AddMonths(Max(Date), -14))) & '<=' &Date(MonthEnd(AddMonths(Max(Date), -14))))"} Sales) +

                        Sum({<Date = {"$(='>=' & Date(MonthStart(AddMonths(Max(Date), -15))) & '<=' &Date(MonthEnd(AddMonths(Max(Date), -15))))"} Sales))/2