6 Replies Latest reply: Jun 26, 2012 3:23 AM by Jaspal Singh

# YTD Calculation

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

try this:

Sum(

{

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

Amount

)

• ###### YTD Calculation

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.

• ###### YTD Calculation

use below code month should in number

Sum(

{

<Year = {\$(=Max(Year))}, Month = {"<=\$(=max(Montthno))}">}

Amount

)

hope this helps

• ###### Re: YTD Calculation

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=,

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

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

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