Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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)))"}>}
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?
sunindia Can you help?
Working on it
The problem is I have to consider each month as (month+priormonth)/2..so for feb..(jan+feb)/2..
not sure how to calculate ytd in this case
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
(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
Sunny,
So I have to create the formula for each month? is there any other way to dynamically?
Anyone have idea how to calculate it dynamically?