Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
Yes, a date is selected.
The value doesn't really matter as such, it is just for the sake of demonstrating the issue
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)
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)?