"nested aggregation not allowed"

[#1]

I want to count the numbers of sales leads in each month. Originally, the report was to start with the current month, and letting the user select up to 11 contiguous months going back.

The formula used for each month was

=sum(if(InMonth(STARTDATE,today(1),-X),1,0)), where "X" would be replaced by 1, 2, 3, etc. months back. This worked perfectly.

User came back and wanted to extend it to any group of contiguous months. They want to use a field I calculate in my script as YearMonth as the selection field. (YearMonth = Year(StartDate)&'-'&num(Month(StartDate),'00') e.g. 201504, 201511

So, I changed the formula to replace "today(1)" with a user-selectable date. I created a variable called "vDate" from this:

Date(MakeDate(left(max(YearMonth),4),right(max(YearMonth),2)),'MM/DD/YY')

Set up a text box to check, selected a bunch of months, and the value was perfect.

So, decided to roll it into the first formula as:

=sum(if(InMonth(STARTDATE,MonthStart($(vDate),0),0),1,0))

This creates an error message:

"Error in expression: nested aggregation not allowed".

Is the 'nested aggregation' the fact that I'm pulling "max(YearMonth)" in my variable declaration? If this isn't allowed, is there another way to approach this problem?

thanks,

Kevin

[#2]

oops sorry - I took the hyphen out of my year month. Shouldn't make a difference though, but the formula is

Year(StartDate)&Num(Month(StartDate),'00'), which does yield 201412, 201506, etc.

[swuehl · Accepted solution]

You can define your variable with a leading equal sign:

=Num(MakeDate(left(max(YearMonth),4),right(max(YearMonth),2)))

or maybe just

=max(StartDate)

(no need to format the date if you want to input it to another function)

Then you should be able to use

=sum(if(InMonth(STARTDATE, vDate ,0),1,0))

[#4]

Thanks, Stefan!