6 Replies Latest reply: Jun 26, 2012 3:23 AM by jaspal.icon RSS

    YTD Calculation

    Anirban Guha

      Hi All,  I have two sets of Data ( Previous Year sales and current year sales) on a monthly basis for eg- Jan11 to dec11 and jan12 to dec12 side by side . I need to calclulate YTD for them. For eg- Previous Year April sales to current year april sales. I need a solution so that it automatically calculates the remaining month's YTD whenever i put the data in them.

        • YTD Calculation
          jaspal.icon

          try this:

          Sum(

          {

          <Year = {$(=Max(Year))}, Month = {$(=Month(vTodaysDate))}>}

          Amount

             )

            • YTD Calculation
              Anirban Guha

              Hi Jaspal- I tried the above set statement but its not working. Let me brief you a bit more about my YTD calculation its actually (Current Year Sales-Previous Year Sales)/Current Year Sales and this sales figure is a cumulative one. For eg- if its for the month of April..i need to add the figures from Jan to Apr last year and jan to Apr current yearand then do the calculation.

            • Re: YTD Calculation
              Iyyappan v

              Hi,

               

                Use the expression in prevuious year when you select month it shows previous year current selection months data defaultly its show previous year whole data

               

              =   Sum({<Year=,

                                      NumDate={'>=$(=(Num(YearStart(AddYears((selectedDate),-1)))))                                                                                   <=$(=(Num(AddYears((selectedDate),-1))))'}>}Data)

               

              Use the expression in current year .

               

              =  Sum({<Year=,

                               NumDate={'>=$(=(Num(YearStart((selectedDate),-1))))

                                       <=$(=(Num((selectedDate),-1)))'}>}Data)

               

              Where selectedDate = MakeDate(Max(Year),Max(Month),Max(Day))

               

              Regards,

              Iyyappan.

                • Re: YTD Calculation
                  Anirban Guha

                  Hi All , Thanks for the replies. However i want to ask you regarding the above case. I have a situation in which i want YTD for 2 regions from Jan - Apr and for 1 region Jan- Mar. How do i go about it in a single set statement? Is there any way of doing that so that when i click on the region it automatically calculates for the above time span? In short- I want if the region is Europe or Asia, then calculate till Jan to Mar and if the region is North AMerica or any other region , then calculate till Jan to Apr.  Thanks- Anirban

                    • Re: YTD Calculation
                      jaspal.icon

                      Hi Anirban,

                      For a region you have a fix months range, which you already know. So you can have some variable say:

                      vStartDate and vEndDate

                      Now say you want to to Show 1 Jan 2012 to 31 Mar 2012

                      then set variable value:

                      vStartDate= Date(MakeDate('1-Jan'&vSelectedYear))

                      vEndDate=Date(MakeDate(if(Region=US,'31-Mar', if(Region=Europe, '30-Apr'))&vSelectedYear))

                      Now in your chart you can set the expression like:

                      Sum(If(SalesDate>=vStartDate and SalesDate<=vEndDate, Sales_Value))

                      Hope this will help

                       

                      Regards

                      Jaspal