Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
try this:
Sum(
{
<Year = {$(=Max(Year))}, Month = {$(=Month(vTodaysDate))}>}
Amount
)
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.
use below code month should in number
Sum(
{
<Year = {$(=Max(Year))}, Month = {"<=$(=max(Montthno))}">}
Amount
)
hope this helps
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.
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
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