Count how many IDs have a sum(value)<100 between two dates

[jakobjensen]

I have some fields, "ID", "orderdate" and "value".

I want to count, how many IDs have a total sum less than 1000 within the last (chosen) year.

I have tried the following

count({<id={"=sum({<orderdate.autoCalendar.Date={$(= '>=' & addmonths(orderdate.autoCalendar.Date,-12) & '<=' & orderdate.autoCalendar.Date)}>} value)<1000"}>} distinct id)

but it just returns "0" all the time.

Can some one explain why? 

[sunny_talwar]

Do you have one date selected when you are trying to look at this? If not, may be you need to use Max(Date) like this

Count({<id = {"=Sum({<orderdate.autoCalendar.Date = {""$(='>=' & AddMonths(Max(orderdate.autoCalendar.Date), -12) & '<=' & Max(orderdate.autoCalendar.Date))""}>} value) < 1000"}>} DISTINCT id)

Also, do you need <100 or <1000, please fix accordingly

[jakobjensen]

Yes, a date is selected.

The value doesn't really matter as such, it is just for the sake of demonstrating the issue

[sunny_talwar]

Got it, did your try this?

Count({<id = {"=Sum({<orderdate.autoCalendar.Date = {""$(='>=' & AddMonths(Max(orderdate.autoCalendar.Date), -12) & '<=' & Max(orderdate.autoCalendar.Date))""}>} value) < 1000"}>} DISTINCT id)

[jakobjensen]

It very much seems like it does! 

Two questions:

1) How come the two double-quotes are needed in the date expression?

2) Can it be extended to the sum being in an interval (e.g 100-200)?