Set Analysis - YTD on Selected Month

[#1]

I am trying to create a YTD field so that the column will only show the YTD values given the current month selection. For example, if you select "March" (a date field) the column will sum up January, February and March. The calculation will change depending on the current selection. I have seen some posts, but no sample codes. I cannot seem to get it to sum a range when only one month is selected.

[johnw · Accepted solution]

I don't know if this is exactly what you're asking for, but it is a YTD total based on the maximum month currently selected (so if no month is selected, it is a normal YTD). Here's the expression:

=sum({<Month={'>=$(=date(yearstart(max(Month)),'MMM-YY')) <=$(=date(max(Month),'MMM-YY'))'}>}Value)

[#3]

Thanks John!

I was trying to get the "<=" sign to work but could not get it to take. I had to modify what you gave me and got it to work. Below is my modified version where MonthNum is the number of the month:

=sum({$ < [Group_ID] = {'B'}, Year = {$(=Year(Today()))}, MonthNum = {">= {'1'} <=$(=max(MonthNum))"}, Month= >}Hours)

Thanks again!

[#4]

thanks John, that helped me too (Y)

[rohit214]

Hi John,

If my Fiscal year starts from 1-apr-2012 to 31-03-3012 then

what will be the expression for same condition???

Thanks & regards

rohit

[#6]

Hi Rohit

then you just change the MonthNum = {">= {'1'} part to 4.

[#7]

Hi John,

I'm trying to set a sheet activation trigger for a Year to Date sales chart. I simply want the trigger to select all months from January to the current month, but the syntax is killing me!

I tried (for example):

=sum({<CloseMonth = {"<= month(today()"}>}1)

and

=sum({<CloseMonth = {"CloseMonth <= month(today()"}>}1)

In both cases, the syntax checker said the expression was OK, but when I leave and re-enter the sheet, nothing is selected in the CloseMonth listbox. "CloseMonth" is a dual field, which shows as text, but has the standard numerical value. Any thoughts?