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